Map Visual & Displaying YoY Changes in Moving Averages

[Dave1mo1]

Hello,

I'm trying to represent changes in moving averages, comparing the current year to the prior year. My measures seem good and are represented accurately on a matrix (below). However, when there are no units ordered from a specific state on the prior day (our data is always a day behind), there is no fill on the map for those states. For example, on the screenshot below:

• The slicer is set to interact with just the matrix and shows Arkansas's YoY% change as -7.05%, but that value is not showing up on the filled map.
• The date filter on the map visual is set for "Relative date" -> is in the last 1 days (to show the most recent moving average data, which is yesterday's).
• You can see the blank for yesterday on my "Total Units" measure, which is what Arkansas and Wyoming (the other blank state) have in common. When I change the date filter to 6/23, Arkansas is no longer blank.

Can anyone help??? I'll put my DAX below as well for these measures. I've been coming at this problem off-and-on for weeks and keep hitting my head against a wall. If there's anything I can do to better lay out the information below, please let me know. I know it's a lot of information.

• Invoiced - Unit MA % Change YoY = divide ([Invoiced - CY Unit MA], [Invoiced - PY Unit MA], 0)-1
• Invoiced - CY Unit MA =calculate ([Invoiced - Total Units], DATESBETWEEN('FY Date Table'[Date], MIN('FY Date Table'[Date])-[Moving Average - 1],min('FY Date Table'[Date])))/'Dynamic Moving Average'[Dynamic Moving Average Value]
  
  
• Invoiced - PY Unit MA = calculate([Invoiced - CY Unit MA], SAMEPERIODLASTYEAR('FY Date Table'[Date]))
  

• Invoiced - Total Units = sum( Sales Fact Tables'[Units])

[Anonymous]

Hi,@Dave1mo1 

If you have found suitable solutions, please share them as it will help more users with similar problems.
Or you can mark the valid suggestions provided by other users as solutions

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi,@Dave1mo1 . I am glad to help you.
According to your description, you are experiencing a situation where a specific continent on the map is not populated when the measure does not calculate the corresponding value.

Your measure is working fine, but when there is no corresponding data for a continent from the previous day, it is not shown on the map visual object.

I think you should consider creating a first new measure without changing the original one, and process the data for it: if the corresponding continent doesn't have data from the previous day, set a default value for it (it should be empty).

Check the results of the original measure calculation, if not empty, it will display the results of the measure calculation, if it is empty, it can be used to distinguish between the other data of the default value (such as 0 or other placeholders) If the measure itself needs to be aggregated calculation, it is recommended to use 0,  as not to affect the final aggregation of total results.

I hope my suggestions  give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Dave1mo1]

Hi, Carson.

Thanks for your response. It looks to me like the issue is caused by there being no data, at all, for these specific "ship to states" in the fact table on the dates that are blank on the map. For example, I updated the file to include yesterday's orders, and now Arkansas is filled, but North Dakota & Idaho are not. The root cause seems to be that there were no rows on the fact table with those corresponding Ship To locations on 6/25, even though the measures are clearly being calculated for these days. Below is an updated map for today.

Can you provide any other tips in regards to this issue? I've tried adjusting the measures to add "+0," but that doesn't seem to resolve the issue. It adjusts the "Total Units" column to be "0," but the state's conditional formatting is still blank, and the tool tip for "YoY% Change" doesn't show when I hover.

[Anonymous]

Hi,@Dave1mo1 

Thank you for your reply, and I'm sorry to say that the advice provided earlier didn't work very well.

In fact, as you said, the problem is in the data source itself: the DAX code is calculating null results because some continents don't have the previous day's data. This is not due to a programming error in the measure or calculate column you created, so it is tricky to work with data that is not available directly from power BI.

I recommend that you use the IF function or the switch function to set the result of an empty calculation to a default value such as -999 or some other unlikely (and highly visible) result for cases where there is no actual data from the previous day.

For the matrix or table in the system automatically aggregated Total value, you can use the ISINCOPE function to artificially modify the final Total calculation results.

like this:

Using IF with the ISINCOPE function

This will not affect your original calculation.

You can then individually set up measures with special default values in Map visual, e.g. if the measure has a special value of -999, it will be marked by displaying the area in a specific color.

The absence of data in the data source itself is tricky for trying to modify the expected results directly in power BI dektop, as it is a problem with the data itself and not with the code written
Looking forward to your reply!

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Dave1mo1]

Hi,

I know this is a delayed response - my apologies. I ended up creating a dimensional table that includes each of the regions, a column with yesterday's date, and a key for each of the dimensions that the map can be filtered on. It was kind of a backwards way to do it, but seems to be working in my testing.

Thanks again for the help.