Forum Discussion

MageVortex's avatar
MageVortex
Helper I
4 years ago

Rolling Average quick measure breaks with Card Visualization

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.
Need Help

13 Replies

  • TheoC's avatar
    TheoC
    Community Champion
    4 years ago

    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's avatar
      MageVortex
      Helper I
      4 years ago

      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's avatar
        TheoC
        Community Champion
        4 years ago

        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's avatar
    MageVortex
    Helper I
    4 years ago

    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's avatar
      TheoC
      Community Champion
      4 years ago

      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's avatar
        MageVortex
        Helper I
        4 years ago

        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.