Forum Discussion
Dynamic YTD Based On Slicer
Hello,
A bit newer to power BI here. I'm trying to create a measure that calculates YTD based on a slicer. I can't attach the dashboard because of privacy issues, but I will do my best to describe the corresponding tables.
I have a table called "Revenue" that has the following columns:
Year
Month
Account
Scenario
Value
Month Number (related function with another table)
There is another table called Months that has three columns called month number, month, and date_format (in a proper date format). This table has a relationship with revenue, and drives the related funtion on the other table.
There is a slicer on my dashboard for date_format (months).
What I'm trying to do is dynamically calculate a YTD revenue based on the month chosen on the slicer, but I can't do it. Essentially, when "April" is chosen, it should be the sum of all correponding revenue less than or equal to the month number column on my revenue table, but I can't get it to work properly. This measure will be put onto a matrix and organized by "unit", which is another table with a relationship to revenue. Below is the function I've tried to use, but I can't get it to work properly.
Rev YTD Test = calculate(sum(Revenue_COGS[Value]),filter(all(Revenue_COGS),Revenue_COGS[Scenario]="ACTUAL" & Revenue_COGS[Year] = "2021" & Revenue_COGS[Account] = "Revenue" & Revenue_COGS[Month Number] <= value(selectedvalue(Months[Date_Format].[Month]))))
If anyone could help me that would be amazing, if need be I can send the pbi file with dummy data.
Thanks in advance.
Hi asteinbach ,
Modify your 2 measures as below:
Revenue_Calc_2021_Actual = Calculate(sum('Revenue_COGS'[Value]),'Revenue_COGS'[Account] = "Revenue",'Revenue_COGS'[Year]=2021,'Revenue_COGS'[Scenario] = "ACTUAL",FILTER(Revenue_COGS,DATE('Revenue_COGS'[Year],Revenue_COGS[Month Number],1)<=SELECTEDVALUE('Months'[Date_Format]))EBITDA_Actual = (Revenue_COGS[Revenue_Calc_2021_Actual] - Calculate(sum(Costs[Value]),Costs[Year]=2021,Costs[Scenario]="ACTUAL",FILTER('Costs', DATE('Costs'[Year],'Costs'[Month Number],1)<=SELECTEDVALUE('Months'[Date_Format]))) + [Dep_Amo_Impair_Actual])And you will see:
As for the orientation from Department_Hiearchy to Revenue_COGS is single and one to many,so when you use month from table Months,the data cant be filtered correctly.That's why you have to add the month filter to the expressions.
Check my .pbix file attached.
Best Regards,
KellyDid I answer your question? Mark my post as a solution!
8 Replies
- Greg_DecklerCommunity Champion
asteinbach Hard to be specific without sample/example data.
You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000
Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TITHW/m-p/434008 - v-kelly-msftCommunity Support
Hi asteinbach ,
Pls share your pbi file with dummy data.
Best Regards,
KellyDid I answer your question? Mark my post as a solution!
- asteinbachFrequent Visitor
Let me know if this link works. Essentially, the calculation should (theoretically) be a calculate based on the selectedvalue of the slicer. So for "May", it should be the sum of all values equal to or before May in the table. However, I couldn't get that to work.
- v-kelly-msftCommunity Support
Hi asteinbach ,
Modify your 2 measures as below:
Revenue_Calc_2021_Actual = Calculate(sum('Revenue_COGS'[Value]),'Revenue_COGS'[Account] = "Revenue",'Revenue_COGS'[Year]=2021,'Revenue_COGS'[Scenario] = "ACTUAL",FILTER(Revenue_COGS,DATE('Revenue_COGS'[Year],Revenue_COGS[Month Number],1)<=SELECTEDVALUE('Months'[Date_Format]))EBITDA_Actual = (Revenue_COGS[Revenue_Calc_2021_Actual] - Calculate(sum(Costs[Value]),Costs[Year]=2021,Costs[Scenario]="ACTUAL",FILTER('Costs', DATE('Costs'[Year],'Costs'[Month Number],1)<=SELECTEDVALUE('Months'[Date_Format]))) + [Dep_Amo_Impair_Actual])And you will see:
As for the orientation from Department_Hiearchy to Revenue_COGS is single and one to many,so when you use month from table Months,the data cant be filtered correctly.That's why you have to add the month filter to the expressions.
Check my .pbix file attached.
Best Regards,
KellyDid I answer your question? Mark my post as a solution!
- v-kelly-msftCommunity Support
Hi asteinbach ,
Have you checked my last reply?Is your issue solved now?
Best Regards,
KellyDid I answer your question? Mark my post as a solution!