Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi guys,
I have sales numbers per accounts per day of the last 3 years. And what I need to visualise is the comparison of the sum of sales up to the rolling date VS the same LYTD for 2021 VS 2020 of the same dates.
ie.
Totals only of these periods---> | Jan 1st - To-same date as current (May 10) | Jan 1st - To-same date as current (May 10) | Jan 1st - To-date (May 10) |
Customer Name | 2020 | 2021 | 2022 |
A | Sum Total Sales | Sum Total Sales | Sum Total Sales |
B | Sum Total Sales | Sum Total Sales | Sum Total Sales |
C | Sum Total Sales | Sum Total Sales | Sum Total Sales |
D | Sum Total Sales | Sum Total Sales | Sum Total Sales |
I can't make Parallelperiod() work to it... may you please kindly help?
Solved! Go to Solution.
@Adore921 , Try like example with date table
try with year on column on matrix
calculate(Sum('order'[Qty]), filter('Date' , format('Date'[Date], "MMDD") <= format(today(), "MMDD") ))
or
measures like
LYTD QTY forced=
var _max = date(year(today())-1,month(today()),day(today()))
return
if(max('Date'[Date])<=_max, CALCULATE(Sum('order'[Qty]),DATESYTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max), blank())
//OR
//CALCULATE(Sum('order'[Qty]),DATESYTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max)
//TOTALYTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,year),'Date'[Date]<=_max)
2nd Last YTD QTY forced=
var _max = date(year(today())-2,month(today()),day(today()))
return
if(max('Date'[Date])<=_max, CALCULATE(Sum('order'[Qty]),DATESYTD(dateadd('Date'[Date],-2,year)),'Date'[Date]<=_max), blank())
//OR
//CALCULATE(Sum('order'[Qty]),DATESYTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max)
//TOTALYTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,year),'Date'[Date]<=_max)
YTD QTY forced=
var _max1 = today() //or maxx(allselected('Order'),'order'[Date])
var _max = format(_max,"MMDD")
return
calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),filter('Date', format('Date'[Date],"MMDD")<=_max))
@Adore921 , Try like example with date table
try with year on column on matrix
calculate(Sum('order'[Qty]), filter('Date' , format('Date'[Date], "MMDD") <= format(today(), "MMDD") ))
or
measures like
LYTD QTY forced=
var _max = date(year(today())-1,month(today()),day(today()))
return
if(max('Date'[Date])<=_max, CALCULATE(Sum('order'[Qty]),DATESYTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max), blank())
//OR
//CALCULATE(Sum('order'[Qty]),DATESYTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max)
//TOTALYTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,year),'Date'[Date]<=_max)
2nd Last YTD QTY forced=
var _max = date(year(today())-2,month(today()),day(today()))
return
if(max('Date'[Date])<=_max, CALCULATE(Sum('order'[Qty]),DATESYTD(dateadd('Date'[Date],-2,year)),'Date'[Date]<=_max), blank())
//OR
//CALCULATE(Sum('order'[Qty]),DATESYTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max)
//TOTALYTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,year),'Date'[Date]<=_max)
YTD QTY forced=
var _max1 = today() //or maxx(allselected('Order'),'order'[Date])
var _max = format(_max,"MMDD")
return
calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),filter('Date', format('Date'[Date],"MMDD")<=_max))
User | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |