Forum Discussion

trevrobwhite2's avatar
trevrobwhite2
Advocate II
2 years ago
Solved

Previous Spend for Matrix

I have a matrix which displays the current spend and I'm trying to add the previous spend, the matrix drills so I have to work out the context, all of that works the issue is if the previous peroid is outside the date filter I either don't get the data or an extra column based on my two DAX measures.

 

This doesn't give me extra column but if the previous peroid is outside of the filter it displays blank:

Previous Spend = 
    IF(
        ISINSCOPE('Medium Query'[Date].[Month]),
        IF (MAX('Medium Query'[Date].[Date]) > DATE(YEAR(TODAY()), MONTH(TODAY()), 01), BLANK(),
            CALCULATE(
                SUM('Medium Query'[Spend]), 
                DATEADD('Medium Query'[Date].[Date], -1, MONTH)
            )
        ),
        IF(
            ISINSCOPE('Medium Query'[Date].[Quarter]),
            IF (MAX('Medium Query'[Date].[Date]) > DATE(YEAR(TODAY()), (ROUNDUP(MONTH(TODAY()) / 3,0) * 3) + 1, 01), BLANK(),
                CALCULATE(
                    SUM('Medium Query'[Spend]), 
                    DATEADD('Medium Query'[Date].[Date], -1, QUARTER)
                )
            ),
            CALCULATE(
                SUM('Medium Query'[Spend]), 
                DATEADD('Medium Query'[Date].[Date], -1, YEAR)
            )
        )
    )

 

This query is preferred as it works but it adds an extra column and I'm not sure how to prevent it:

Previous Spend = 
    IF(
        ISINSCOPE('Medium Query'[Date].[Month]),
        IF (MAX('Medium Query'[Date].[Date]) > DATE(YEAR(TODAY()), MONTH(TODAY()), 01), BLANK(),
            CALCULATE(
                SUM('Medium Query'[Spend]), 
                REMOVEFILTERS('Medium Query'[Date]),
                DATEADD('Medium Query'[Date].[Date], -1, MONTH)
            )
        ),
        IF(
            ISINSCOPE('Medium Query'[Date].[Quarter]),
            IF (MAX('Medium Query'[Date].[Date]) > DATE(YEAR(TODAY()), (ROUNDUP(MONTH(TODAY()) / 3,0) * 3) + 1, 01), BLANK(),
                CALCULATE(
                    SUM('Medium Query'[Spend]), 
                    REMOVEFILTERS('Medium Query'[Date]),
                    DATEADD('Medium Query'[Date].[Date], -1, QUARTER)
                )
            ),
            CALCULATE(
                SUM('Medium Query'[Spend]), 
                REMOVEFILTERS('Medium Query'[Date]),
                DATEADD('Medium Query'[Date].[Date], -1, YEAR)
            )
        )
    )

My 'Medium Query'[Date] slicer is set to between if it makes any difference

 

Any pointers would be greatfully recieved.

Need Help
  • rajendraongole1's avatar
    rajendraongole1
    2 years ago

    Hi trevrobwhite2 - Hope your date slicer is set up correctly and that your model's date table covers the range required for all comparisons.

    for previous spend, can you check the below measure:

    Previous Spend =
    VAR CurrentMaxDate = MAX('Medium Query'[Date].[Date])
    VAR PreviousPeriodSpend =
    IF(
    ISINSCOPE('Medium Query'[Date].[Month]),
    CALCULATE(
    SUM('Medium Query'[Spend]),
    DATEADD('Medium Query'[Date].[Date], -1, MONTH),
    REMOVEFILTERS('Medium Query'[Date]) // Allows looking at previous period's data without current period filter
    ),
    IF(
    ISINSCOPE('Medium Query'[Date].[Quarter]),
    CALCULATE(
    SUM('Medium Query'[Spend]),
    DATEADD('Medium Query'[Date].[Date], -1, QUARTER),
    REMOVEFILTERS('Medium Query'[Date])
    ),
    CALCULATE(
    SUM('Medium Query'[Spend]),
    DATEADD('Medium Query'[Date].[Date], -1, YEAR),
    REMOVEFILTERS('Medium Query'[Date])
    )
    )
    )
    RETURN
    IF (
    CurrentMaxDate <= TODAY() && CurrentMaxDate >= MIN('Medium Query'[Date].[Date]),
    PreviousPeriodSpend,
    BLANK()
    )

     

    Hope it work s, let know if any and share sample data too.