Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello,
I'm struggling with getting a cumulative budget per day. The budget is fixed data coming from an Excel file. It's broken up into a few categories:
Budget per month, per sales reps, per product category.
I'm able to create a matrix visual and have the current sales next to the Monthly Budget and have it filter by Month, Sales Rep and Product. Below is an example of what I currently have. I was able to verfy all the data is correct.
The sales group would like a cumulative per day as well. I'm not sure if I have an incorrect relationship behind the scenes, bad date table or possibly a missing date column in my budget table to create the correct relationship? The results I get right now show incorrect numbers that don't validate against the budget. I inherited a crazy pbix file and I have a feeling the budget isn't being spread across the correct date table. I can share the file if need be. Any help or guidance is appreciated.
You are not showing data by day. What Cummaltive means here.
Datesmtd/totalmtd and datesytd/totalytd can help you. you need to have a date table.
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date]))
You can use isfiltered or isinscope, to control MTD and YTD.
https://docs.microsoft.com/en-us/dax/isinscope-function-dax
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Datanaut Connect on Linkedin
Hi @Anonymous,
When does your budget reset - monthly, quarterly, all time cumulative, etc ?
Proud to be a Super User!
Hi @Anonymous ,
Try this:
Cumulative =
VAR __MTD =
TOTALMTD ( [Total], 'Table'[Date] )
VAR __YTD =
CALCULATE (
[Total],
FILTER (
ALLEXCEPT ( 'Table', 'Table'[Year] ),
'Table'[Date] <= MAX ( 'Table'[Date] )
)
)
RETURN
SWITCH (
TRUE (),
HASONEVALUE ( 'Table'[Year] ), __YTD,
HASONEVALUE ( 'Table'[Month] ), __MTD
)
Proud to be a Super User!
User | Count |
---|---|
96 | |
87 | |
78 | |
72 | |
68 |
User | Count |
---|---|
113 | |
105 | |
84 | |
65 | |
64 |