Forum Discussion

erikm's avatar
erikm
Frequent Visitor
5 years ago

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

  • 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
    )
    )

  • Anonymous's avatar
    Anonymous
    Not 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.

  • erikm's avatar
    erikm
    Frequent Visitor

    Thank you for the responses. Unfortunately that didn't work. I have provided an example workbook here with a simple dataset and hopefully a clear structure of the issue and what I want to reach.

    Thank you!