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
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:
Date
column, and change its format as desired.Month
column, and Sort by column
on MonthNum
.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:
DateTable
to your "expenses" table.