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

Question

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

Answer 1

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:

• Select the Date column, and change its format as desired.
• Select the Month column, and Sort by column on MonthNum.
• Right-click on the DateTable and select Mark as date table, and set the Date column to Date.

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:

• Create a one-to-many relationship from the DateTable to your "expenses" table.
• Add a new table called "Dim Color" (as an example), with a column of all the distinct values for Color.
• Create a one-to-many relationship from the "Dim Color" table each of the other two "fact tables" (Amount table, Expenses table).
• Lastly, use "Dim Color" table for your matrix/slicers/filters.

Your model should look similar to this: