Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
vika160
Helper III
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.

 

 

 

with Values.PNGno Values.PNG

 

 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.
Appreciate your help.

 Thank you.

 

 

 

1 ACCEPTED SOLUTION
VijayP
Super User
Super User

@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




Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
MY Blog || My YouTube Channel || Connect with me on Linkedin || My Latest Data Story - Ageing Analysis

Proud to be a Super User!


View solution in original post

8 REPLIES 8
ALLUREAN
Solution Sage
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)
 
 
 
 



Did I answer your question? Mark my post as a solution!


https://allure-analytics.com/
https://www.youtube.com/channel/UCndD_QZVNB_JWYLEmP6KrpA
https://www.linkedin.com/company/77757292/

Proud to be a Super User!




Hi @ALLUREAN,

 

Thank you for your help.

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

vika160_0-1646632867343.png

 

Thanks.

VijayP
Super User
Super User

@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




Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
MY Blog || My YouTube Channel || Connect with me on Linkedin || My Latest Data Story - Ageing Analysis

Proud to be a Super User!


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

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




Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
MY Blog || My YouTube Channel || Connect with me on Linkedin || My Latest Data Story - Ageing Analysis

Proud to be a Super User!


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
Helper III
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

amitchandak
Super User
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.

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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