cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper III

## Help with Fiscal YTD and LYTD calculations

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")

I am wondering what I am missing.

Thank you.

1 ACCEPTED SOLUTION
Super User

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

MY Blog || My YouTube Channel || Connect with me on Linkedin || My Latest Data Story - Ageing Analysis

Proud to be a Super User!

8 REPLIES 8
Solution Sage

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 YTDCALCULATE([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)

https://allure-analytics.com/

Proud to be a Super User!

Helper III

Hi @ALLUREAN,

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

Thanks.

Super User

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

MY Blog || My YouTube Channel || Connect with me on Linkedin || My Latest Data Story - Ageing Analysis

Proud to be a Super User!

Helper III

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

Super User

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!

MY Blog || My YouTube Channel || Connect with me on Linkedin || My Latest Data Story - Ageing Analysis

Proud to be a Super User!

Helper III

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!

Helper III

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

Super User

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors