Forum Discussion
DAX - Using measures to filter chart on specific years
I have the following issue that I don't understand how to solve it. The following scenario exists:
I have a data table that contains subscription data with a date column and a column "balance" that contains either a +1 for a new subscription or -1 for a subscription end. That means, to get the amount of subscriptions on a specific date, I need to do a running total.
To do this, I have added a date slicer with the following values:
From: 01/01/2004
To: 06/29/2021
To get the running total, it works perfectly fine with the following DAX calculation:
balance RT =
VAR RowDate = LASTDATE(Dataset[measureDate])
RETURN
CALCULATE(
SUM('Dataset'[saldo]),
FILTER(
ALLSELECTED('Dataset'),
ISONORAFTER('Dataset'[measureDate], MAX('Dataset'[measureDate]), DESC)
&& YEAR ( 'Dataset'[measureDate] ) = YEAR ( RowDate )
)
)
Next thing I want to do is to put this on a graph and only show data for 2020 and 2021. To do this hardcoded, I changed the above DAX formula to the following:
balance RT =
VAR RowDate = LASTDATE(Dataset[measureDate])
VAR measureDateFrom = DATE(2020,1,1)
RETURN
CALCULATE(
SUM('Dataset'[saldo]),
FILTER(
ALLSELECTED('Dataset'),
ISONORAFTER('Dataset'[measureDate], MAX('Dataset'[measureDate]), DESC)
&& YEAR ( 'Dataset'[measureDate] ) = YEAR ( RowDate )
&& 'Dataset'[measureDate].[Date] > measureDateFrom
)
)
This also works perfectly fine. But obviously I don't want to have that 2020 hardcoded in that third line and therefore I have created a separate measure:
DATE(YEAR(LASTNONBLANK(Dataset[measureDate], Dataset[measureDate]))-1, 1, 1)
This works fine when I display this specific measure in a card visualization. Now, when I integrate this into the above DAX formula, it doesn't work. It seems like the date is not calculated properly because all the data is still displayed in the chart. It looks like this "2020" that is the outcome of this separate measure, is not properly evaluated in the above formula.
Would anybody be able to help out here? Thank you in advance!
3 Replies
- amitchandakSuper User
erikm , Based on what I got. Try a measure like
balance RT =
VAR RowDate = LASTDATE(Dataset[measureDate])
VAR measureDateFrom = DATE(year(RowDate)-1,1,1)
RETURN
CALCULATE(
SUM('Dataset'[saldo]),
FILTER(
ALLSELECTED('Dataset'),
ISONORAFTER('Dataset'[measureDate], MAX('Dataset'[measureDate]), DESC)
&& YEAR ( 'Dataset'[measureDate] ) = YEAR ( RowDate )-1
&& 'Dataset'[measureDate].[Date] > measureDateFrom
)
) - AnonymousNot applicable
Hi erikm ,
Do you want to calculate the cumulative sum? You can try measure like
Running Total COLUMN = CALCULATE ( SUM ( 'All Web Site Data (2)'[UniquePageviews] ), ALL ( 'All Web Site Data (2)' ), 'All Web Site Data (2)'[Date] <= EARLIER ( 'All Web Site Data (2)'[Date] ) )Reference: Cumulative Total
Power BI: Calculating Cumulative Totals for Time Periods
If you are still confused, please provide me with more details about your problem. It is best to provide some dummy data and show the corresponding expected results.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.