Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I am using a SSAS OLAP as data source and have a couple of YTD measures which I want to use in Power BI
My PBI reports are all based on a month slicer (typically the business want to look at the data from a month perspective)
YTD are defined with the YTD MDX function shich means that I am using a date heirarchy year - month
This works all fine but it requires me to include a slicer for year (or a heirarchy slicer) in the report
If possible I want to remove the year slicer (space is pressures in the reports)
Any ideas?
Workarounds in PBI?
Posibble to define a YTD measure without teh YTD function?
YTD will not work without year. Year To Date is related to year so it definetly required year to calculate YTD. We can't calculate YTD using only month, Right?
Hope, I made it clear.
Thanks, I understand that.
But given that I have a year - month field (e.g. 2017 january)
Are there any workarounds? either in Power BI or by changing the MDX
I think, no.
Because you have to use a year to calculate YTD. So whenever you try to filter the YTD based on Date, it needs year to calculated YTD for that particular period.
Let's say you have data for 2016 and 2017. You have calculated YTD.
You have month slicer.
For example, you selected Nov in the slicer. How do you whether it's Nov 2016 or Nov 2017.
How do you calculate the YTD without know the year?
Hope, I'm clear.
you are clear, thanks
But since the year-month field contains the year I thought it might be possible to extract the year from that field and use if for the YTD
Hi @jockefe,
But since the year-month field contains the year I thought it might be possible to extract the year from that field and use if for the YTD
If I understand you correctly, you should be able to create a new measure to calculate the YTD with the year-month field. The formula below is for your reference.
measure_YTD = VAR lastDay = MAX ( 'Date'[Date] ) VAR year = YEAR ( lastDay ) RETURN CALCULATE ( SUM ( 'Table1'[Values] ), FILTER ( ALL ( 'Date' ), YEAR ( 'Date'[Date] ) = year && 'Date'[Date] <= lastDay ) )
Regards
thanks! That what I mean.
But I am using SSAS OLAP (Live connection) which means that I need to translaet that calculation ito MDX
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |