Rolling Average quick measure breaks with Card Visualization

[MageVortex]

Hello,

I have a quick measure to calculate a rolling average. It was working last month, but now in January, the Card visual shows a 'Blank' value instead of the actual value. When I place the measure in a table, it calculates and displays the value, but in the Card visual, it says blank.In November and updated in January

 -------------------------------------------------------------------------------------------------------------------------------------------

The measure for that is 

roll6SupToRate no ReOrg =

DIVIDE([roll6SupTo no ReOrg], [roll6Sup])

 -------------------------------------------------------------------------------------------------------------------------------------------

[roll6SupTo no ReOrg] also returns a (Blank) value, it's set up as follows:

roll6SupTo no ReOrg =

if(CALCULATE([Sup Total])=0,BLANK(),

IF(

    ISFILTERED('Dates'[dateValue]),

    ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),

    VAR __LAST_DATE = ENDOFMONTH('Dates'[dateValue].[Date])

    VAR __DATE_PERIOD =

        DATESBETWEEN(

            'Dates'[dateValue].[Date],

            STARTOFMONTH(DATEADD(__LAST_DATE, -6, MONTH)),

            __LAST_DATE

        )

    RETURN

        AVERAGEX(

            CALCULATETABLE(

                SUMMARIZE(

                    VALUES('Dates'),

                    'Dates'[dateValue].[Year],

                    'Dates'[dateValue].[QuarterNo],

                    'Dates'[dateValue].[Quarter],

                    'Dates'[dateValue].[MonthNo],

                    'Dates'[dateValue].[Month]

                ),

                __DATE_PERIOD

            ),

            CALCULATE([supCountTOCalc no Re-Org], ALL('Dates'[dateValue].[Day]))

        )

))

 -------------------------------------------------------------------------------------------------------------------------------------------

[supCountTOCalc no Re-Org] does not return a blank value
[Roll6Sup] *does* return a blank value, though it too shows a value in the table, which is visible in the picture above.

2 Months ago everything worked perfectly and a non-refreshed version of this file is still perfect.  Anyone know what I need to do? Trying to clean the data so I can post it would probably take a few hours, so I'm hoping there's a magical solution that doesn't require that.

[MageVortex]

Because I pulled the data in by month for those queries, and my dates table is by day. It's tied to it so it matches the 1/1/2021 in the month field to the 1/1/2021 dates table 'dateValue'.

I can change the date/time to date, but if I do it on one of the tables, it breaks my measures. Not entirely sure why without doing a lot more work.

I did some checking, and if I set my 'Dates' table to end and not update past 12/31/2021, then my 'Blank' measure works again. It's only once I let the Dates update to 1/1/2022.

[TheoC]

Hi @MageVortex 

Is your date table formulated using DAX?

If so, how did you formulated the first and last date of the Date table? You may need to update the end date of your date table (e.g. change the end date to be in the future).

[MageVortex]

I went through the process of creating a Power BI DAX based

Calendar = CALENDAR(today()-900,today())
Once I set up the model connections, and set it up, I get teh same 'Blank' result.

[TheoC]

@MageVortex 

As good as Power BI is, setting up a calendar in that manner unfortunately will not work very effectively unless relationships and relevance have been established.  Instead of working on Dates, Calendars, etc., can you tell me what is the outcome that you are trying to achieve?  By this, I mean, what is "roll6SupTo no ReOrg" trying to calculate?

[MageVortex]

The total/overall average of the 6 month rolling average of the Supervisor turnover excluding a certain segment of those supervisors.  As the original image shows, it still calculates it, but now it won't display in the KPI box.
This is what it looks like pre 2022

This is it now

Again, if I change the date range to be before 1/1/2022, it works, but once I include 2022, it no longer generates that total.

[MageVortex]

The date table is a SQL data pull from a date table we maintain which includes a cornicopia of additional potential data points around each of those dates. For instance, markers for if 1/5/2022 is a weekend day or weekday, or if it is teh first monday of the month, first weekend day of the month, or has a holiday on it, etc etc. 
As to changing the end date of the table to go into the future, I can't understand why that could be the issue as it worked perfectly fine not going into the future in 2021.  When I change my date query to stop on 12-31-2021, the measure works perfectly fine, but when I then let it go into 2022, it immediately stops working and goes Blank.  This didn't happen at any point last year when it was the beginning or middle of the month.

I've been working on creating a DAX based calendar and creating a hierarchy, but having to manually create it is annoying as it automatically created the hierarchy for me with my sql query based dates table, and when I try to use this custom made DAX based calendar, instead of whoing all of the months going back historically, it only shows the last 6 months....sigh....each attempt creates 5 more questions to solve.

[TheoC]

Hi @MageVortex 

How have you structured your date table?  A lot of times, when a date table is first created, manual inputs are used on the start and end dates.  I recommend checking the end date to ensure it has rolled over into 2022.

Hope this helps.

Theo 🙂

[MageVortex]

It is set up the same way, goes out +60 days from todays present day (pulls from a table that goes out a few years) so the set up is consistent. I made sure the set up was consistent.

[TheoC]

That's good to hear!  With regard to your main measure, what is your [Sup Total] value? It is used in your current measure in the first line: "if(CALCULATE([Sup Total])=0,BLANK()"

[MageVortex]

Sup Total = sum('Monthly Sup Count'[Supervisor Count])

This chart has data displayed like the following:

[TheoC]

Hi @MageVortex 

Okay, I think it may have to do with the current structure of your model and the relationships between your fact table and date table.  Are you able to send me a screenshot of the Model view screen that shows the relationships?

Thanks heaps mate. 

Theo 🙂

[MageVortex]

This may confuse more than help when I draw the arrows to show the connections

[TheoC]

@MageVortex - why does the fact tables have "Month" instead of date?  I noticed in the earlier data you provided, the Month column was a "Date / Time" format.  Can you change this to Date format only or is this like that for a reason?