YTD vs PriorYr=full year report toggle

[Dellis81]

Hello.   I am attempting to create financial statements using the matrix visual, and date slicer.    I do have measures in place that are generating the desired report calculations on a Full year basis.    However, my users are now wanting a report toggle allowing a selection of report interval.    PriorYr=Full Yr, or PriorYr=YTD.

Below is a layout of slicers, and matrix visual with year as column

My initial thought was to design a calculatedtable with desired date intervals, and then use the calctable as a filter within calculate.    I have gone down the road of DateAdd, but that doesn't seem to work well in a matrix utilizing years as columns.  Then - I found the DatesBetween

BS1 YTD Test Cascading Report Value = 

Var ReportToggle = SELECTEDVALUE('Report Period'[Report Period],"Year to Date")

Var YTDMonth = month(SELECTEDVALUE('CALENDAR'[Date]))
Var YTDDay = DAY(SELECTEDVALUE('CALENDAR'[Date]))
vAR YTDYr = yEAR(SELECTEDVALUE('CALENDAR'[Date]))
Var calcDatetable =CALCULATETABLE('CALENDAR',DATESBETWEEN('CALENDAR'[Date],[MinDate],date(YTDYr,YTDMonth,YTDDay)))

I'm needing help in evaluating the proper dateinterval (calculatedtable) for use as a filter within a calculate function.  Thank you

[v-lili6-msft]

hi  @Dellis81 

You could refer to this blog:

https://community.powerbi.com/t5/Community-Blog/Dynamically-change-the-information-within-a-visual-v...

Regards,

Lin

[amitchandak]

Try With Date Function-

YTD QTY = TOTALYTD(Sum('order'[Qty]),'Date'[Date])
LYTD QTY = TOTALYTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,year))
Previous Year = CALCULATE(SUM('order'[Qty]), PREVIOUSYEAR('Date'[Date]))

YTD QTY forced=
var _max = today()
return
calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),'Date'[Date]<=_max)
//calculate(TOTALYTD(Sum('order'[Qty]),'Date'[Date]),filter('Date','Date'[Date]<=_max))

LYTD QTY forced=
var _max = date(year(today())-1,month(today()),day(today()))
return
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)

This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))

YTD QTY forced=
var _max = maxx('order',[Order date])
return
calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),'Date'[Date]<=_max)
//calculate(TOTALYTD(Sum('order'[Qty]),'Date'[Date]),filter('Date','Date'[Date]<=_max))

LYTD QTY forced=
var _max1 =maxx('order',[Order date])
var _max = date(year(_max1)-1,month(_max1),day(_max1))
return
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)

[Dellis81]

Thank you - my initial posting got sent prematurely.

I have gone down the trail you are suggesting with the LYTD or same Sameperiodlast year, but was finding my years were off by one within the matrix.   Am I missing something in how this might work?

I was really hoping to have a report toggle allowing hte selection of either time interval.   Apprecreate your thoughts!

[Greg_Deckler]

Is something missing?

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-TIT...

Shows both time intelligence and non-time intelligence ways of doing YTD, etc.

[Dellis81]

Yes, sorry - initial post was sent prematurely - not knowing what I was doing?   Reviewing your measures, I believe the magic sauce is deducting 1 from the current year.   How would that work in a matrix with multiple years.?

Your thoughts are greatly appreciated!

[amitchandak]

Hope the last update was for me

That depends on usage. I would have only shown Value and change % , if I would have taken a year in the column of matrix