Forum Discussion
Time - intelligence: YTD/ LYTD
Hi, auxilio99357
Yes, if you use time intelligence functions, such as TOTALYTD, then you need to select a specific date, such as 2020. If the context does not have a specific year,it will select the date with the largest date, so an error occurs.
In a line chart with a year, the time intelligence function should be no problem, but if it used in the table, you need to rewrite the measure and specify a specific year.
Like:
2020YTD = CALCULATE ( SUM ( Sales[Units] ), YEAR ( DimDates[Date] ) = 2020 )If you only put the month on the slicer, it will include all the corresponding months of the year, then there will be loopholes in the logic you said. Please try to understand what I said and make changes.
Best Regards
Janey Guo
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Community SupportHi, auxilio99357
According to your description, I think you must know that the value of measure will change with the context. It is under the action of each visual, slicer and filter. You use the time intelligence function, and the formula isn't rigorous, so the expected value doesn't appear in different visuals(The formulas you use are the most basic. Once the context is complicated, wrong values will appear.).
Can you share some fake sample data and your desired result? So we can help you soon.
Best Regards
Janey Guo
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi v-janeyg-msft 😊
Thank you for your help, I will try to be as clear as possible
I have two data tables, the first one is like this:
(note that all of the sales of a month are charged to the first day of it)
The other one is a common date dimension table.
The YTD and LYTD measures I want to display it on a simple table like this:
The things is that I want the LYTD measure to sum the months of the previous year till the month we currently are. In the example data the current month is april 2020, so the LYTD formula value should be ´0´ for every client, but is not.
And the other thing I cant solve is the issue with the PROM measure, I want to display it in a table like this (the photo is from the original data):
The idea is that you choose a month from the slicer and the PROM measure calculates the average of the previous 12 month not inlcuding the current month.
Please let me know if something is not clear.
Thank you!
- v-janeyg-msft
Hi, auxilio99357
Yes, if you use time intelligence functions, such as TOTALYTD, then you need to select a specific date, such as 2020. If the context does not have a specific year,it will select the date with the largest date, so an error occurs.
In a line chart with a year, the time intelligence function should be no problem, but if it used in the table, you need to rewrite the measure and specify a specific year.
Like:
2020YTD = CALCULATE ( SUM ( Sales[Units] ), YEAR ( DimDates[Date] ) = 2020 )If you only put the month on the slicer, it will include all the corresponding months of the year, then there will be loopholes in the logic you said. Please try to understand what I said and make changes.
Best Regards
Janey Guo
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I understand what you said and I rewrite the measure YTD and it works ok.
But now i don´t know how to change de LYTD measure, because if I do the same with LYTD like this:
2020LYTD =
CALCULATE ( SUM ( Sales[Units] ), YEAR ( DimDates[Date] ) = 2019 )The measure will sum up the whole 2019 instead of the parcial months. I can filter the months manually but my goal is to write a measure that calculate the results automatically.
Thats why I add the page filter (Year (from DimDates) = 2020) and expect that this measures work:
YTD = TOTALYTD( SUM ( Sales[Units] ), DimDates[Date] )
LYTD = CALCULATE( [YTD] , SAMEPERIODLASTYEAR ( DimDates[Date] ) )but again LYTD sums up the whole 2019.
Can you think another why to solve it?
Regards!
Auxilio99357
