topic Re: All of my queries returning blank! in DAX Commands and Tips

All of my queries returning blank!

SimoneHud — Fri, 25 Apr 2025 13:17:08 GMT

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

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

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.

Re: All of my queries returning blank!

lbendlin — Fri, 25 Apr 2025 14:14:44 GMT

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-Forum/ba-p/963216
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: All of my queries returning blank!

antfr99 — Fri, 25 Apr 2025 14:15:10 GMT

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

Re: All of my queries returning blank!

ZhangKun — Fri, 25 Apr 2025 14:47:50 GMT

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

Re: All of my queries returning blank!

SimoneHud — Fri, 25 Apr 2025 14:50:40 GMT

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?

Re: All of my queries returning blank!

antfr99 — Fri, 25 Apr 2025 16:01:54 GMT

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 :

Re: All of my queries returning blank!

SimoneHud — Fri, 25 Apr 2025 17:45:49 GMT

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 )

Re: All of my queries returning blank!

antfr99 — Mon, 28 Apr 2025 07:06:17 GMT

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 )

Re: All of my queries returning blank!

SimoneHud — Fri, 06 Jun 2025 07:19:52 GMT

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

Re: All of my queries returning blank!

antfr99 — Fri, 06 Jun 2025 08:04:12 GMT

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

Re: All of my queries returning blank!

SimoneHud — Fri, 06 Jun 2025 08:11:37 GMT

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

Re: All of my queries returning blank!

antfr99 — Fri, 06 Jun 2025 08:57:37 GMT

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

Re: All of my queries returning blank!

SimoneHud — Fri, 06 Jun 2025 09:03:40 GMT

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

Re: All of my queries returning blank!

antfr99 — Fri, 06 Jun 2025 10:35:14 GMT

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

Re: All of my queries returning blank!

SimoneHud — Fri, 06 Jun 2025 10:48:11 GMT

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.

Re: All of my queries returning blank!

SimoneHud — Fri, 06 Jun 2025 11:12:53 GMT

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!

Re: All of my queries returning blank!

antfr99 — Fri, 06 Jun 2025 12:00:21 GMT

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

Re: All of my queries returning blank!

SimoneHud — Fri, 06 Jun 2025 13:48:50 GMT

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

Re: All of my queries returning blank!

antfr99 — Fri, 06 Jun 2025 14:13:53 GMT

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

Re: All of my queries returning blank!

SimoneHud — Fri, 06 Jun 2025 14:23:56 GMT