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.
Hi , I want to analyze last 5 years data. and I created YTD for that. If am not selecting any year in the slicer, It is showing current year data. Instead of that, I want to show YTD of last 5 years data. Please help on this.
Here is my formula
VAR LastSale = MAXX( ALL(ACX_OENS[Document Date] ), OENS[Document Date] )
VAR YTDSales = TOTALYTD( [OE], 'Calendar'[Date] )
RETURN
IF( MIN( 'Calendar'[Date] ) <= LastSale, YTDSales, BLANK() )
Solved! Go to Solution.
Below formula is working fine, But I want to replace MAX date with Todays date.
Any idea
VAR FirstDateSelected =
CALCULATE ( MIN ( 'Date'[Date] ), ALLSELECTED ( 'Date' ) )
VAR Result =
CALCULATE (
<measure>,
DATESBETWEEN (
'Date'[Date],
FirstDateSelected,
MAX ( 'Date'[Date] )
)
)
RETURN Result
Hi @Anonymous,
By YTD of last 5 years do you mean you want to show the sales from 01-JAN-2014 to 11-SEP-2019. If that been the case, you can try the below measure,
YTDSales = CALCULATE(SUM(Sales), Filter(Tablename, Date >= DATE(YEAR(TODAY())-5,1,1) && Date <= TODAY()))
Hope this helps!! Kindly let me know for any concerns.
Thanks,
Ani
In year slicer, If I select a specific year, YTD is showing correctly. But If am not selecting any year, then YTD is showing for the last year. Instead of that, I want to show YTD of all the years.
Hi @Anonymous
Please find the attached pbix file.
Hope it helps!!. If not then please share some screenshots of results and the expected results.
Thanks,
Ani
Below formula is working fine, But I want to replace MAX date with Todays date.
Any idea
VAR FirstDateSelected =
CALCULATE ( MIN ( 'Date'[Date] ), ALLSELECTED ( 'Date' ) )
VAR Result =
CALCULATE (
<measure>,
DATESBETWEEN (
'Date'[Date],
FirstDateSelected,
MAX ( 'Date'[Date] )
)
)
RETURN Result
@Anonymous
It seems that you may just use TODAY instead.
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 |
---|---|
117 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
108 | |
90 | |
64 |