All of my queries returning blank!

[SimoneHud]

Please help me! I'm VERY new to DAX and trying to do a KPI card to show MoM results to no avail. I have found (on here) some DAX coding which I've successfully used to get my results. In a table, when using my date function, under the values, I can clearly see the actuals and then another column which shows me I've correctly calculated the prev month. I can also do a formula that correctly shows this month which also works. However, when I try to show it in a card, it shows as blank. 

Here are some codes I've tried:

Month On Month = CALCULATE(Sum('KPI 1  Seafront Enforcement'[Actual]), PREVIOUSMONTH('KPI 1  Seafront Enforcement'[Seafront Enforcement Dates].[Date]))

or

Prev Month Acs = CALCULATE(SUM('KPI 1  Seafront Enforcement'[Actual]), PREVIOUSMONTH('KPI 1  Seafront Enforcement'[Seafront Enforcement Dates].[Date]))

or

New Month Over Month Measure = CALCULATE(SUM('KPI 1  Seafront Enforcement'[Actual]),PREVIOUSMONTH(LASTDATE('KPI 1  Seafront Enforcement'[Seafront Enforcement Dates].[Date])))

No matter what I do, I end up with a blank card. 

Here is the data sample:

PLEASE will someone help me. I've spent all day trying to sort this out myself! Thanks so much. 

[ZhangKun]

Without the external filter, the PREVIOUSMONTH function will return the minimum date of all dates, which is blank (because there is no date before the minimum date). Because of the blank filter, the final result is blank.

Time intelligence functions are affected by context, so it is best to provide an example file (with a demonstration of the results you want).

[Ant_Fri · Accepted solution]

Hi @SimoneHud 

I suggest try this measure  :

Month_on_Month_Variance = 
VAR CurrentMonthActual =
    CALCULATE(
        SUM('KPI 1 Seafront Enforcement'[Actual]),
        FILTER(
            ALL('KPI 1 Seafront Enforcement'),
            YEAR('KPI 1 Seafront Enforcement'[Seafront Enforcement Dates]) = YEAR(MAX('KPI 1 Seafront Enforcement'[Seafront Enforcement Dates]))
            &&
            MONTH('KPI 1 Seafront Enforcement'[Seafront Enforcement Dates]) = MONTH(MAX('KPI 1 Seafront Enforcement'[Seafront Enforcement Dates]))
        )
    )

VAR PreviousMonthActual =
    CALCULATE(
        SUM('KPI 1 Seafront Enforcement'[Actual]),
        FILTER(
            ALL('KPI 1 Seafront Enforcement'),
                        (YEAR('KPI 1 Seafront Enforcement'[Seafront Enforcement Dates]) = YEAR(EDATE(MAX('KPI 1 Seafront Enforcement'[Seafront Enforcement Dates]), -1)))
            &&
            (MONTH('KPI 1 Seafront Enforcement'[Seafront Enforcement Dates]) = MONTH(EDATE(MAX('KPI 1 Seafront Enforcement'[Seafront Enforcement Dates]), -1)))
        )
    )

RETURN
IF(
    ISBLANK(PreviousMonthActual),
    BLANK(),
    CurrentMonthActual - PreviousMonthActual
)

You should the get the Month on Month variance in your table and in a card  :

Above was created with the assistance of AI.

Hope this helps

Antonio

[SimoneHud]

Hi @Antoni You are a genius. So I copied and amended the code, thank you. However, I am still getting a blank output. What could it be that I'm doing incorrectly?

Week_on_Week_Variance_Seafront_Trading = 
VAR CurrentDate = MAX('KPI 4 Seafront - Trading Income'[Week Ending].[Date])
VAR CurrentWeekActual =
    CALCULATE(
        SUM('KPI 4 Seafront - Trading Income'[Actual]),
        FILTER(
            ALL('KPI 4 Seafront - Trading Income'),
            YEAR('KPI 4 Seafront - Trading Income'[Week Ending].[Date]) = YEAR(CurrentDate) &&
            WEEKNUM('KPI 4 Seafront - Trading Income'[Week Ending].[Date], 2) = WEEKNUM(CurrentDate, 2)
        )
    )

VAR PreviousWeekDate = CurrentDate - 7

VAR PreviousWeekActual =
    CALCULATE(
        SUM('KPI 4 Seafront - Trading Income'[Actual]),
        FILTER(
            ALL('KPI 4 Seafront - Trading Income'),
            YEAR('KPI 4 Seafront - Trading Income'[Week Ending].[Date]) = YEAR(PreviousWeekDate) &&
            WEEKNUM('KPI 4 Seafront - Trading Income'[Week Ending].[Date], 2) = WEEKNUM(PreviousWeekDate, 2)
        )
    )

RETURN
IF(
    ISBLANK(PreviousWeekActual),
    BLANK(),
    CurrentWeekActual - PreviousWeekActual
)

[Ant_Fri]

Ok, can you try this amended code ? I assume the code for the month and quarter is working fine ?

Week_on_Week_Variance 2 = 
VAR CurrentWeekActual =
    CALCULATE(
        SUM('KPI 4 Seafront - Trading Income'[Actual]),
        FILTER(
            ALL('KPI 4 Seafront - Trading Income'),
            YEAR('KPI 4 Seafront - Trading Income'[Week Ending]) = YEAR(MAX('KPI 4 Seafront - Trading Income'[Week Ending]))
            &&
            WEEKNUM('KPI 4 Seafront - Trading Income'[Week Ending], 2) = WEEKNUM(MAX('KPI 4 Seafront - Trading Income'[Week Ending]), 2)
        )
    )

VAR PreviousWeekActual =
    CALCULATE(
        SUM('KPI 4 Seafront - Trading Income'[Actual]),
        FILTER(
            ALL('KPI 4 Seafront - Trading Income'),
            YEAR('KPI 4 Seafront - Trading Income'[Week Ending]) = YEAR(MAX('KPI 4 Seafront - Trading Income'[Week Ending]) - 7)
            &&
            WEEKNUM('KPI 4 Seafront - Trading Income'[Week Ending], 2) = WEEKNUM(MAX('KPI 4 Seafront - Trading Income'[Week Ending]) - 7, 2)
        )
    )

RETURN
IF(
    ISBLANK(PreviousWeekActual),
    BLANK(),
    CurrentWeekActual - PreviousWeekActual
)

 

[SimoneHud]

SUPER helpful. Thanks Antonio. If I wanted to do WoW, would it be the same but replace the word Month with Week, and same with QoQ?

[Ant_Fri]

Ok, great to hear that its helpful !

There are some amendments for QoQ and WoW as per below :

Quarter_on_Quarter_Variance = 
VAR CurrentQuarterActual =
    CALCULATE(
        SUM('KPI 1 Seafront Enforcement'[Actual]),
        FILTER(
            ALL('KPI 1 Seafront Enforcement'),
            YEAR('KPI 1 Seafront Enforcement'[Seafront Enforcement Dates]) = YEAR(MAX('KPI 1 Seafront Enforcement'[Seafront Enforcement Dates])) &&
            QUARTER('KPI 1 Seafront Enforcement'[Seafront Enforcement Dates]) = QUARTER(MAX('KPI 1 Seafront Enforcement'[Seafront Enforcement Dates]))
        )
    )

VAR PreviousQuarterDate = EDATE(MAX('KPI 1 Seafront Enforcement'[Seafront Enforcement Dates]), -3)

VAR PreviousQuarterActual =
    CALCULATE(
        SUM('KPI 1 Seafront Enforcement'[Actual]),
        FILTER(
            ALL('KPI 1 Seafront Enforcement'),
            YEAR('KPI 1 Seafront Enforcement'[Seafront Enforcement Dates]) = YEAR(PreviousQuarterDate) &&
            QUARTER('KPI 1 Seafront Enforcement'[Seafront Enforcement Dates]) = QUARTER(PreviousQuarterDate)
        )
    )

RETURN
IF(
    ISBLANK(PreviousQuarterActual),
    BLANK(),
    CurrentQuarterActual - PreviousQuarterActual
)

and for week on week :

Week_on_Week_Variance = 
VAR CurrentDate = MAX('KPI 1 Seafront Enforcement'[Seafront Enforcement Dates])
VAR CurrentWeekActual =
    CALCULATE(
        SUM('KPI 1 Seafront Enforcement'[Actual]),
        FILTER(
            ALL('KPI 1 Seafront Enforcement'),
            YEAR('KPI 1 Seafront Enforcement'[Seafront Enforcement Dates]) = YEAR(CurrentDate) &&
            WEEKNUM('KPI 1 Seafront Enforcement'[Seafront Enforcement Dates], 2) = WEEKNUM(CurrentDate, 2)
        )
    )

VAR PreviousWeekDate = CurrentDate - 7

VAR PreviousWeekActual =
    CALCULATE(
        SUM('KPI 1 Seafront Enforcement'[Actual]),
        FILTER(
            ALL('KPI 1 Seafront Enforcement'),
            YEAR('KPI 1 Seafront Enforcement'[Seafront Enforcement Dates]) = YEAR(PreviousWeekDate) &&
            WEEKNUM('KPI 1 Seafront Enforcement'[Seafront Enforcement Dates], 2) = WEEKNUM(PreviousWeekDate, 2)
        )
    )

RETURN
IF(
    ISBLANK(PreviousWeekActual),
    BLANK(),
    CurrentWeekActual - PreviousWeekActual
)

to get the below :

[lbendlin]

If you want to use Time Intelligence functions like PREVIOUSMONTH then you need to use a designated Calendar table in your data model. They don't work well in fact tables.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...