Help with Fiscal YTD and LYTD calculations

[vika160]

Hi All,

I need to build totals for YTD and LYTD for Fiscal year 

I  built measuments for YTD  and LYTD using diff. formulas.  

The problem I m experiencing:

both visuals for YTD work as they shoud

visuals for LYTD show values only when  2 years  selected together. (2022 and 2021)

When only 2022 year is selected, visuals for LYTD show BLANK value.

 Please, note I have test data only for 2021 and 2022 fiscal year.

Fact table is linked to tblData.

 Measures  YTD:

LIC Totals YTD = CALCULATE(distinctCount(zTestLic[LICENCE_ID]),

            zTestLic[Approval] <> "Void",DATESYTD(zTestLic[LicCreateDate],"05/31"))

LIC Totals YTD 1 = CALCULATE(distinctCount(zTestLic[LICENCE_ID]),

            zTestLic[Approval] <> "Void")

Measures for LYTD :

LIC Totals YTD = CALCULATE(distinctCount(zTestLic[LICENCE_ID]),

            zTestLic[Approval] <> "Void",DATESYTD(zTestLic[LicCreateDate],"05/31"))

LIC Totals YTD 1 = CALCULATE(distinctCount(zTestLic[LICENCE_ID]),

            zTestLic[Approval] <> "Void")

test file 

I am wondering what I am missing.

Appreciate your help.

 Thank you.

[ALLUREAN]

Hi, @vika160 

I would suggest using your filter zTestLic[Approval] <> "Void" to be in your default calculation like.

LIC Totals = CALCULATE(distinctCount(zTestLic[LICENCE_ID]), zTestLic[Approval] <> "Void"))

Then create your YTD measure like:

LIC Totals YTD = CALCULATE([LIC Totals],DATESYTD(zTestLic[LicCreateDate],"05/31"))

And finally LYTD based on upper YTD shifted 1 year back when selection year from your slicer:

LIC Totals LYTD = CALCULATE([LIC Totals YTD]), DATESYTD(DATEADD(zTestLic[LicCreateDate], -1,YEAR)

[vika160]

Hi @ALLUREAN,

Thank you for your help.

I am still getting the same issue ( Blank value for LYTD ) if I just select 2022:

Thanks.

[VijayP · Accepted solution]

@vika160 

Step 1 
Have Date Table
Step 2

Create an Aggregation measure 
Total of Column = Sum(ColumnName)
Step 3

YTD = Calculate ([Total of Column], DatesYtd(Datesdim[Date])

Step 4 
Previous Year Value = Calculate([Total of column],Sameperiodlastyear(DAtedim[Date]))

Step 5
Preivous YTD = Caculate([Previous year Value], DAtesytd(Datedim[Date]))
you can now compare the values the way you want . Please share your Kudos

[vika160]

Hi @VijayP 

Thank you for your suggestion. I will try it tomorrow.

I am wondering why there is should be extra steps for creating ggregation measure, I actually used the aggregation measure for YTD. but it do not have issues with YTD. I will try to do this for LYTD. Curious to try  

 thanks

[VijayP]

@vika160 

While using the Aggregation within calculate will create repeated Row Context / Filter Context which may cause report latency ! Always better to have the in Separate aggregation and use further like Calculate or any ! Hope that answers ! 

If you find this as solution please mark it as solution and Share your Kudos!

[vika160]

HI @VijayP ,

I tried your solution and I get the sane resuls - totals for previos year show as Blank unless 2 years are selected.

If I select 2022 and 2021 - everything works

If I select just 2022 - data for YTD shows properly, data forr 2021 - both fields show Blank value.

test data1 

I updated all measure following your suggestions. If you can chaeck it out and let me know if there is anything I am missing, it would be great!
Thank you for your help!

[vika160]

Hi @amitchandak .

I tried to useboth  the date table and fact table in measure  and both did not work for Previous year.

if the fact table linked to Date table it should not matter which table to use  in calculations.

Thank you

[amitchandak]

@vika160 , Try like these with date table

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"5/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"5/31"))

and refer

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5b...
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.