Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I created an area graph that shows the current YTD info that is grouped by month. Since I do not have a date range specified, the graph contnues to expand as the data is refreshed each night. The year is specidifed in another field.
The challenge I have is with having last year’s data only show up to the same day last year. Since there is data for the entire year, it shows all the data for last year. I do not want to change a date range every day, I would like for it to automatically move forward as each day passes. Any help would be greatly apprecaited.
Column type: Date
Direct query
Solved! Go to Solution.
Hi,
I assume that:
Revenue=SUM(Data[Sales])
YTD sales=CALCULATE([Revenue],DATESYTD(Calendar[Date],"31/12")
YTD sales last year=CALCULATE([YTD Sales],SAMEPERIODLASTYEAR(Calendar[Date]))
In your visual, drag the YTD sales and YTD saes last year fields. Also, as you can observe, i have assumed that your Financial Year ending is 31/12. Please chage that to your actual year ending date and month.
Hope this helps.
Hi,
I assume that:
Revenue=SUM(Data[Sales])
YTD sales=CALCULATE([Revenue],DATESYTD(Calendar[Date],"31/12")
YTD sales last year=CALCULATE([YTD Sales],SAMEPERIODLASTYEAR(Calendar[Date]))
In your visual, drag the YTD sales and YTD saes last year fields. Also, as you can observe, i have assumed that your Financial Year ending is 31/12. Please chage that to your actual year ending date and month.
Hope this helps.
Hi Ken-BI
I am not an expert, maybe there is a simpler way... but I found here this amazing DAX formula that calculates previous year sales UP TO the corresponding day of current year sales YTD
PY Last Day Selection =
VAR LastDaySelection =
LASTNONBLANK ('Date'[Date], [Total Sales] )
VAR CurrentRange =
DATESBETWEEN ( 'Date'[Date], MIN ( 'Date'[Date] ), LastDaySelection )
VAR PreviousRange =
SAMEPERIODLASTYEAR ( CurrentRange )
RETURN
IF (
LastDaySelection >= MIN ( 'Date'[Date] ),
CALCULATE ( [Total Sales], PreviousRange )
)
It returns 2016 sales up to the last sales day in 2017, ie Dec-1 2016. Tomorrow it should refresh automaticaly to Dec-2 2016(assuming there is sales on Dec-2, 2017)
let me know if works
How about write a new measure for last year.
=if([this year measure] >0,[lastyear measure])
User | Count |
---|---|
128 | |
109 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |