Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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:
Thank you.
Solved! Go to Solution.
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
Proud to be a Super User!
Hi, @vika160
I would suggest using your filter zTestLic[Approval] <> "Void" to be in your default calculation like.
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:
Thanks.
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
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
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!
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!
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
@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.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
86 | |
76 | |
74 | |
56 | |
45 |
User | Count |
---|---|
117 | |
105 | |
77 | |
66 | |
64 |