Forum Discussion
HASONEFILTER for drillthrough
Hi everyone,
I have found a solution to my problem.
If you always want to have the latest month of a year, you could use this formula:
msr_YourName = CALCULATE(SUM(tableName; LASTDATE(dateColumn))Then, if the date hierarchy (use a well designed date table) is created with only year and months, the legend is selected and the msr_YourName is used as the values, it gave me exactly what I was looking for.
To everyone involved, thank you very much for your help!
BestJ
HI v-xuding-msft,
thank you very much for your reply!
This is almost what I want and your example works as I would expect it. But, if I have multiple years, I have a different output to what I would expect:
When one clicks on the double arrow (down), one comes down a hierarchy level. So far so good, but I have multiple different years, I don't want to see the sum (monthly aggregate) of all years but only the year I was clicking on.
From what I know, to drill down on one specific Year the single arrow (down) is to use. But when I do this, I only see the month 12 but not the other months.
One additional thing: How could I accomplish such a thing, when the last month of the latest year not december is? (The report is on a monthly basis and each month, there is a new month. Thus, I would like to display 2019-12, but 2020-01). Is it possible to do so? (As far as I understood the code, when month 12 is not available, the whole year will not be displayed).
Thank you in advance,
Best regards
J
Hi everyone,
I have found a solution to my problem.
If you always want to have the latest month of a year, you could use this formula:
msr_YourName = CALCULATE(SUM(tableName; LASTDATE(dateColumn))
Then, if the date hierarchy (use a well designed date table) is created with only year and months, the legend is selected and the msr_YourName is used as the values, it gave me exactly what I was looking for.
To everyone involved, thank you very much for your help!
Best
J