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. 

[antfr99 · 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

[antfr99 · Accepted solution]

Hi @SimoneHud 

Ok, I recommend using the following measures (note that they work best when your data is limited to the current year).

1. Use the measure below to see how the amount builds up month over month within a quarter

QTD_Actual_Fiscal = 
VAR CurrentDate = MAX('KPI 4 Seafront - Trading Income'[Week Ending])


VAR FiscalQuarterStart =
    SWITCH(
        TRUE(),
        MONTH(CurrentDate) IN {4,5,6}, DATE(YEAR(CurrentDate), 4, 1),
        MONTH(CurrentDate) IN {7,8,9}, DATE(YEAR(CurrentDate), 7, 1),
        MONTH(CurrentDate) IN {10,11,12}, DATE(YEAR(CurrentDate), 10, 1),
        DATE(YEAR(CurrentDate) - 1, 1, 1)
    )

RETURN
CALCULATE(
    SUM('KPI 4 Seafront - Trading Income'[Actual]),
    FILTER(
        ALL('KPI 4 Seafront - Trading Income'),
        'KPI 4 Seafront - Trading Income'[Week Ending] >= FiscalQuarterStart &&
        'KPI 4 Seafront - Trading Income'[Week Ending] <= CurrentDate
    )
)

2. Use the below to show QTD ( up to last full month of current quarter ) : 

QTD_Actual_LastFullMonth_Only = 
VAR TodayDate = TODAY()


VAR CurrentFiscalQuarterStart =
    SWITCH(
        TRUE(),
        MONTH(TodayDate) IN {4,5,6}, DATE(YEAR(TodayDate), 4, 1),
        MONTH(TodayDate) IN {7,8,9}, DATE(YEAR(TodayDate), 7, 1),
        MONTH(TodayDate) IN {10,11,12}, DATE(YEAR(TodayDate), 10, 1),
        DATE(YEAR(TodayDate) - 1, 1, 1)
    )


VAR CurrentFiscalQuarterEnd = EOMONTH(CurrentFiscalQuarterStart, 2)


VAR LastFullMonthEnd = EOMONTH(TodayDate, -1)
VAR LastFullMonthStart = DATE(YEAR(LastFullMonthEnd), MONTH(LastFullMonthEnd), 1)


VAR QuarterToDateTotal =
    CALCULATE(
        SUM('KPI 4 Seafront - Trading Income'[Actual]),
        FILTER(
            ALL('KPI 4 Seafront - Trading Income'),
            'KPI 4 Seafront - Trading Income'[Week Ending] >= CurrentFiscalQuarterStart &&
            'KPI 4 Seafront - Trading Income'[Week Ending] <= LastFullMonthEnd
        )
    )


RETURN
IF(
    MONTH(MAX('KPI 4 Seafront - Trading Income'[Week Ending])) = MONTH(LastFullMonthEnd) &&
    YEAR(MAX('KPI 4 Seafront - Trading Income'[Week Ending])) = YEAR(LastFullMonthEnd),
    QuarterToDateTotal,
    BLANK()
)

3. Use the below to show QTD ( including current month of current quarter ) : 

QTD_Actual_CurrentMonth_Only = 
VAR TodayDate = TODAY()


VAR CurrentFiscalQuarterStart =
    SWITCH(
        TRUE(),
        MONTH(TodayDate) IN {4,5,6}, DATE(YEAR(TodayDate), 4, 1),
        MONTH(TodayDate) IN {7,8,9}, DATE(YEAR(TodayDate), 7, 1),
        MONTH(TodayDate) IN {10,11,12}, DATE(YEAR(TodayDate), 10, 1),
        DATE(YEAR(TodayDate) - 1, 1, 1)
    )


VAR CurrentMonthEnd = EOMONTH(TodayDate, 0)


VAR CurrentRowDate = MAX('KPI 4 Seafront - Trading Income'[Week Ending])


VAR IsCurrentMonth =
    MONTH(CurrentRowDate) = MONTH(TodayDate) &&
    YEAR(CurrentRowDate) = YEAR(TodayDate)


VAR QuarterToDateUpToCurrentMonth =
    CALCULATE(
        SUM('KPI 4 Seafront - Trading Income'[Actual]),
        FILTER(
            ALL('KPI 4 Seafront - Trading Income'),
            'KPI 4 Seafront - Trading Income'[Week Ending] >= CurrentFiscalQuarterStart &&
            'KPI 4 Seafront - Trading Income'[Week Ending] <= CurrentMonthEnd
        )
    )

RETURN
IF(
    IsCurrentMonth,
    QuarterToDateUpToCurrentMonth,
    BLANK()
)

below are the results : 

Antonio

[SimoneHud]

Thank you so much! Do you have a 'buy me a coffee' style account that I can contribute to? You've helped me so much and I am really greatful. Thank you Antonio. 

[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).

[antfr99 · 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
)

[antfr99]

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]

Hey, me again! Are you able to help me return the answer as a percentage please? So I'd like to get a percentage of WoW (Current week v previous week)? Thank you 

[antfr99]

Hi @SimoneHud 

Sure, I suggest the below measure : 

Week_on_Week_Variance % = 
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) || PreviousWeekActual = 0,
    BLANK(),
    DIVIDE(CurrentWeekActual - PreviousWeekActual, PreviousWeekActual)
)

You should get the below result after you amend your measure to percentage format : 

Hope this helps

Antonio 

[SimoneHud]

GENIUS! Can you do MoM and QoQ for me too??? Last requests I PROMISE 🙂

[antfr99]

Hi @SimoneHud 

Glad that it's working so far, see the below suggestions for MoM and QoQ .

>>AI is the genius here, I am just asking it nicely for answers 

Month_on_Month_Variance % = 
VAR CurrentMonthActual =
    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])) &&
            MONTH('KPI 4 Seafront - Trading Income'[Week Ending]) = MONTH(MAX('KPI 4 Seafront - Trading Income'[Week Ending]))
        )
    )

VAR PreviousMonthActual =
    CALCULATE(
        SUM('KPI 4 Seafront - Trading Income'[Actual]),
        FILTER(
            ALL('KPI 4 Seafront - Trading Income'),
            YEAR('KPI 4 Seafront - Trading Income'[Week Ending]) = YEAR(EDATE(MAX('KPI 4 Seafront - Trading Income'[Week Ending]), -1)) &&
            MONTH('KPI 4 Seafront - Trading Income'[Week Ending]) = MONTH(EDATE(MAX('KPI 4 Seafront - Trading Income'[Week Ending]), -1))
        )
    )

RETURN
IF(
    ISBLANK(PreviousMonthActual) || PreviousMonthActual = 0,
    BLANK(),
    DIVIDE(CurrentMonthActual - PreviousMonthActual, PreviousMonthActual)
)

Quarter_on_Quarter_Variance % = 
VAR CurrentQuarterActual =
    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])) &&
            QUARTER('KPI 4 Seafront - Trading Income'[Week Ending]) = QUARTER(MAX('KPI 4 Seafront - Trading Income'[Week Ending]))
        )
    )

VAR PreviousQuarterDate = EDATE(MAX('KPI 4 Seafront - Trading Income'[Week Ending]), -3)

VAR PreviousQuarterActual =
    CALCULATE(
        SUM('KPI 4 Seafront - Trading Income'[Actual]),
        FILTER(
            ALL('KPI 4 Seafront - Trading Income'),
            YEAR('KPI 4 Seafront - Trading Income'[Week Ending]) = YEAR(PreviousQuarterDate) &&
            QUARTER('KPI 4 Seafront - Trading Income'[Week Ending]) = QUARTER(PreviousQuarterDate)
        )
    )

RETURN
IF(
    ISBLANK(PreviousQuarterActual) || PreviousQuarterActual = 0,
    BLANK(),
    DIVIDE(CurrentQuarterActual - PreviousQuarterActual, PreviousQuarterActual)
)

The measures provide the below results : 

Antonio 

[SimoneHud]

OK, at the risk of getting totally told to 'do one'....

Two more (and then my dashboard is complete) This one may be challenging...

Our financial year starts in April. I need to be able to show the QTD total. So how do I tell PowerBI at the appropriate time to only look for data between April 1 - June 30 (if we are in that period) and start reporting again for July 1 - 30 Sept (if we are in that period). 

[antfr99 · Accepted solution]

Hi @SimoneHud 

Ok, I recommend using the following measures (note that they work best when your data is limited to the current year).

1. Use the measure below to see how the amount builds up month over month within a quarter

QTD_Actual_Fiscal = 
VAR CurrentDate = MAX('KPI 4 Seafront - Trading Income'[Week Ending])


VAR FiscalQuarterStart =
    SWITCH(
        TRUE(),
        MONTH(CurrentDate) IN {4,5,6}, DATE(YEAR(CurrentDate), 4, 1),
        MONTH(CurrentDate) IN {7,8,9}, DATE(YEAR(CurrentDate), 7, 1),
        MONTH(CurrentDate) IN {10,11,12}, DATE(YEAR(CurrentDate), 10, 1),
        DATE(YEAR(CurrentDate) - 1, 1, 1)
    )

RETURN
CALCULATE(
    SUM('KPI 4 Seafront - Trading Income'[Actual]),
    FILTER(
        ALL('KPI 4 Seafront - Trading Income'),
        'KPI 4 Seafront - Trading Income'[Week Ending] >= FiscalQuarterStart &&
        'KPI 4 Seafront - Trading Income'[Week Ending] <= CurrentDate
    )
)

2. Use the below to show QTD ( up to last full month of current quarter ) : 

QTD_Actual_LastFullMonth_Only = 
VAR TodayDate = TODAY()


VAR CurrentFiscalQuarterStart =
    SWITCH(
        TRUE(),
        MONTH(TodayDate) IN {4,5,6}, DATE(YEAR(TodayDate), 4, 1),
        MONTH(TodayDate) IN {7,8,9}, DATE(YEAR(TodayDate), 7, 1),
        MONTH(TodayDate) IN {10,11,12}, DATE(YEAR(TodayDate), 10, 1),
        DATE(YEAR(TodayDate) - 1, 1, 1)
    )


VAR CurrentFiscalQuarterEnd = EOMONTH(CurrentFiscalQuarterStart, 2)


VAR LastFullMonthEnd = EOMONTH(TodayDate, -1)
VAR LastFullMonthStart = DATE(YEAR(LastFullMonthEnd), MONTH(LastFullMonthEnd), 1)


VAR QuarterToDateTotal =
    CALCULATE(
        SUM('KPI 4 Seafront - Trading Income'[Actual]),
        FILTER(
            ALL('KPI 4 Seafront - Trading Income'),
            'KPI 4 Seafront - Trading Income'[Week Ending] >= CurrentFiscalQuarterStart &&
            'KPI 4 Seafront - Trading Income'[Week Ending] <= LastFullMonthEnd
        )
    )


RETURN
IF(
    MONTH(MAX('KPI 4 Seafront - Trading Income'[Week Ending])) = MONTH(LastFullMonthEnd) &&
    YEAR(MAX('KPI 4 Seafront - Trading Income'[Week Ending])) = YEAR(LastFullMonthEnd),
    QuarterToDateTotal,
    BLANK()
)

3. Use the below to show QTD ( including current month of current quarter ) : 

QTD_Actual_CurrentMonth_Only = 
VAR TodayDate = TODAY()


VAR CurrentFiscalQuarterStart =
    SWITCH(
        TRUE(),
        MONTH(TodayDate) IN {4,5,6}, DATE(YEAR(TodayDate), 4, 1),
        MONTH(TodayDate) IN {7,8,9}, DATE(YEAR(TodayDate), 7, 1),
        MONTH(TodayDate) IN {10,11,12}, DATE(YEAR(TodayDate), 10, 1),
        DATE(YEAR(TodayDate) - 1, 1, 1)
    )


VAR CurrentMonthEnd = EOMONTH(TodayDate, 0)


VAR CurrentRowDate = MAX('KPI 4 Seafront - Trading Income'[Week Ending])


VAR IsCurrentMonth =
    MONTH(CurrentRowDate) = MONTH(TodayDate) &&
    YEAR(CurrentRowDate) = YEAR(TodayDate)


VAR QuarterToDateUpToCurrentMonth =
    CALCULATE(
        SUM('KPI 4 Seafront - Trading Income'[Actual]),
        FILTER(
            ALL('KPI 4 Seafront - Trading Income'),
            'KPI 4 Seafront - Trading Income'[Week Ending] >= CurrentFiscalQuarterStart &&
            'KPI 4 Seafront - Trading Income'[Week Ending] <= CurrentMonthEnd
        )
    )

RETURN
IF(
    IsCurrentMonth,
    QuarterToDateUpToCurrentMonth,
    BLANK()
)

below are the results : 

Antonio

[SimoneHud]

Hey, sorry, its the last one that I want to use. So all transations up to date. But this is returning "0" for me. The other two options work fine but ideally, its the last one I want as I want to include all income to date. I tried adjusting the last line to include a + but that didn't work unfortunately!

[antfr99]

Hi @SimoneHud 

We could try to debug the 3rd option by testing different parts of the code,  but as you mentioned that the first option is working for you I suggest we amend that one so that it returns only the result for current month ( QTD )  :

QTD_Actual_Fiscal_CurrentMonthOnly = 
VAR CurrentDate = MAX('KPI 4 Seafront - Trading Income'[Week Ending])
VAR TodayDate = TODAY()

VAR IsCurrentMonth =
    MONTH(CurrentDate) = MONTH(TodayDate) &&
    YEAR(CurrentDate) = YEAR(TodayDate)

VAR FiscalQuarterStart =
    SWITCH(
        TRUE(),
        MONTH(CurrentDate) IN {4,5,6}, DATE(YEAR(CurrentDate), 4, 1),
        MONTH(CurrentDate) IN {7,8,9}, DATE(YEAR(CurrentDate), 7, 1),
        MONTH(CurrentDate) IN {10,11,12}, DATE(YEAR(CurrentDate), 10, 1),
        DATE(YEAR(CurrentDate) - 1, 1, 1)
    )

RETURN
IF(
    IsCurrentMonth,
    CALCULATE(
        SUM('KPI 4 Seafront - Trading Income'[Actual]),
        FILTER(
            ALL('KPI 4 Seafront - Trading Income'),
            'KPI 4 Seafront - Trading Income'[Week Ending] >= FiscalQuarterStart &&
            'KPI 4 Seafront - Trading Income'[Week Ending] <= CurrentDate
        )
    ),
    BLANK()
)

result :  

 

Let me know if it works on your side ? 

Antonio

[SimoneHud]

I need it to show for the whole quarter including the current month? So for the whole of April, May and June. Then after 1st July for it to show for all income in July, Aug, Sept

[antfr99]

Can you recreate in an excel sheet and send me a screenshot of your requirement ?  Indicate if you are in June what you expect to see for the year  . Then in another screenshot indicate if you are in Sept what you expect to see for the year  . Thanks

[SimoneHud]

[SimoneHud]

Yes, this works amazing. Thanks. One FINAL request (probably)... could you amend this to return the average percentage of the quarter please Antonio?

[antfr99 · Accepted solution]

Ok thanks for the screenshot , I suggest try the below : 

QTD_Actual_Fiscal_CurrentQuarterOnly = 
VAR CurrentDate = MAX('KPI 4 Seafront - Trading Income'[Week Ending])
VAR TodayDate = TODAY()


VAR FiscalQuarterStart =
    SWITCH(
        TRUE(),
        MONTH(TodayDate) IN {4,5,6}, DATE(YEAR(TodayDate), 4, 1),
        MONTH(TodayDate) IN {7,8,9}, DATE(YEAR(TodayDate), 7, 1),
        MONTH(TodayDate) IN {10,11,12}, DATE(YEAR(TodayDate), 10, 1),
        DATE(YEAR(TodayDate), 1, 1)
    )

VAR FiscalQuarterEnd =
    SWITCH(
        TRUE(),
        MONTH(TodayDate) IN {4,5,6}, DATE(YEAR(TodayDate), 6, 30),
        MONTH(TodayDate) IN {7,8,9}, DATE(YEAR(TodayDate), 9, 30),
        MONTH(TodayDate) IN {10,11,12}, DATE(YEAR(TodayDate), 12, 31),
        DATE(YEAR(TodayDate), 3, 31)
    )


VAR IsInCurrentQuarter =
    CurrentDate >= FiscalQuarterStart &&
    CurrentDate <= FiscalQuarterEnd

RETURN
IF(
    IsInCurrentQuarter,
    CALCULATE(
        SUM('KPI 4 Seafront - Trading Income'[Actual]),
        FILTER(
            ALL('KPI 4 Seafront - Trading Income'),
            'KPI 4 Seafront - Trading Income'[Week Ending] >= FiscalQuarterStart &&
            'KPI 4 Seafront - Trading Income'[Week Ending] <= FiscalQuarterEnd
        )
    )
)

You should get the below which totals per quarter and after each quarter it should reset mainly due to the below variable. Let me know if it works for you.

VAR TodayDate = TODAY()

[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?

[antfr99]

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 :