powerbipowerbi-desktoppowerbi-custom-visuals

Daily, MTD, QTD, YTD Sums as of a Specific Date bifurcated by Specific Field in Slicer


I would like to create a time series analysis with groupings by a column and have the analysis be as of a user selected date, from a slicer, and show daily, mtd, qtd, ytd totals. The values that show up in the daily, mtd, qtd, ytd would only include values up to the date in the slicer. i found this difficult because of dates where there is not a color (grouping), the color drops out of the report and that causes the MTD / QTD / YTD for the aggregate to be missing that value

sample data:

Date Amount Color
1/5/2023 50 red
1/10/2023 55 blue
1/15/2023 60 red
7/1/2023 1 yellow

if i choose 1/5/23 in my slicer, the matrix visual would show

Color Daily MTD QTD YTD
Red 50 50 50 50

if i choose 1/10/23 in the slicer, the report would show

Color Daily MTD QTD YTD
Red 0 50 50 50
Blue 55 55 55 55

if i choose 1/15/23 in the slicer, the report would show

Color Daily MTD QTD YTD
Red 60 110 110 110
Blue 0 55 55 55

if i choose 7/1/23 in the slicer, the report would show

Color Daily MTD QTD YTD
Red 0 0 0 110
Blue 0 0 0 55
Yellow 1 1 1 1

I have tried writing a DAX to calculate a custom column which shows the MTD, QTD, YTD for the single data table as shown above. it works fine unless you choose a date in which the colors are not all represented but have all occured - for example, choosing 1/10/23 in the slicer causes red to not show up as it doesn't have a record for that date, even though it has a MTD , QTD, YTD which contribute to the aggregate table amount.

I tried creating a separate table for all daily permutations but wasn't sure how to get all possible permutations of date and color - for example, that table would have

Date Amount Color
1/5/2023 50 Red
1/5/2023 0 blue
1/10/2023 55 blue
1/10/2023 0 Red
1/15/2023 60 Red
1/15/2023 0 Blue
7/1/2023 0 Red
7/1/2023 0 Blue
7/1/2023 1 Yellow

i have experimented with joining a separate calendar table but don't think i got the syntax right.

this is just sample data, and i plan to use the architecture for many time series data points so would like the layout to be as flexible and adaptable as possible, so i am trying to avoid creating a specific month, quarter, year table of just results.

The problem specifically (i think!) is the lack of continuous data


Solution

  • What you need is a Date table: Set and use date tables in Power BI Desktop.

    Create a new Calculated Table with the following (as an example):

    DateTable = SUMMARIZE(
      CALENDARAUTO(),
      [Date],
      "Year", YEAR([Date]),
      "Month", FORMAT([Date], "MMM"),
      "MonthNum", MONTH([Date]),
      "Year-Month", FORMAT([Date], "yyyy-MM"),
      "Quarter", "Q" & QUARTER([Date]),
      "Year-Quarter", FORMAT([Date], "yyyy-\Q") & QUARTER([Date])
    )
    

    Once created, do the following:

    Now create a one-to-many relationship from the DateTable to your fact/data table.

    Now you can create the following measures, where DAX will take care of the Time intelligence for you.

    Total Amount = SUM('YourTable'[Amount])
    
    Amount Daily = [Total Amount]
    
    Amount MTD = TOTALMTD([Total Amount], 'DateTable'[Date])
    
    Amount QTD = TOTALQTD([Total Amount], 'DateTable'[Date])
    
    Amount YTD = TOTALYTD([Total Amount], 'DateTable'[Date])
    

    Finally, update your date slicer to use the new DateTable.


    Follow-up

    You could create the following measures:

    Total Expenses = SUM('YourTable'[expenses])
    
    Net Amount Daily = [Total Amount] + [Total Expenses]
    
    Net Amount MTD = TOTALMTD([Net Amount Daily], 'DateTable'[Date])
    
    Net Amount QTD = TOTALQTD([Net Amount Daily], 'DateTable'[Date])
    
    Net Amount YTD = TOTALYTD([Net Amount Daily], 'DateTable'[Date])
    

    Note: If your "expenses" table is separate to your "amount" table, then you will need to do the following:

    Your model should look similar to this:
    enter image description here