Forum Discussion
Filter with calculated measures
Hello,
What probably would work perfectly is a separate DateTable with contiguous Dates. Then you could add the DateTable columns to your PivotTable and use it for TOTALYTD.
After trying a little bit this formula gave me the correct result as well:
YTD Revenue:=VAR LatestDate=MAX(FactTable[Datekey]) RETURN CALCULATE(SUM([column]);
CALCULATETABLE(
FILTER(FactTable;FactTable[Datekey]<=LatestDate&&YEAR([Datekey])=YEAR(LatestDate));
ALL(FactTable)
)
)
Hello,
maybe I haven't understood your problem totally.
My first question is why don't you add a timeline instead of separate slicers for month and year?
Revenue YTD=TOTALYTD([Measure],Table[DateKey])
If you want to use slicer instead of timeline.
You can add:
Revenue YTD=IF(HASONEVALUE(Table[Year],
TOTALYTD([Measure],Table[DateKey]),
BLANK())
So it should automatically give you your YTD value if only one year is selected.
Hi,
I am fine with using the below code snippet
Revenue YTD=TOTALYTD([Measure],Table[DateKey])
And this is also giving YTD values for the year selections.
i.e. Say I am having 3 years.
2016
2017
2018
When I select 2018, this gives me YTD values for 2018, When 2017 is selected, YTD values for 2017 is shown and so on.
But my requirement is in addition to the year selection, it should also respond to the month selections made
The Following is the step-by-step process of potential selection of slicers
i.e.
1. Firstly, If 2018 and Apr are selected in slicers, my YTD Value should be the Value from Jan-2018 to Apr-2018
2. Now if my Month slicer is changed to Mar, my YTD Value should be from Jan-2018 to Mar-2018
3. Lastly, If I change my year to 2017, the card should display YTD Value from Jan-2017 to Mar-2017, since I have already selected my Month to March in the previous step.
Hope I am clear this time!!!
