Suddenly missing a month even with Data

[Schwurblini]

Hi Everyone

So i ran into an issue with some measure and a column chart:

As you can see I am missing a Month, there used tho be a column so it was working afew refreshes ago but lately it did not show any data anymore. There are no filters active. My Measures look like this:

The columns are incrementally growing each month and for them i created a separate table:

Cumulated Table Finished Onboardings ECOM = SUMMARIZE('Finished Onboarding', 
      'Finished Onboarding'[Week], 'Finished Onboarding'[registrationTime],
     "Total Onboardings", [Total Onboarding finished eCommerce])

Total onboarding finished eCommerce is this measure:

Total Onboarding finished eCommerce =
CALCULATE ( COUNTROWS ( 'Finished Onboarding' ), 'Finished Onboarding'[productMapping] = "XXX" || 'Finished Onboarding'[productMapping] = "YYY")

In this table i create an measure like this:

CumulOnboardings = 
            CALCULATE ( SUM('Cumulated Table Finished Onboardings ECOM'[Total Onboardings]),
    			FILTER (  
    			ALL ( 'Cumulated Table Finished Onboardings ECOM' ),
        		'Cumulated Table Finished Onboardings ECOM'[registrationTime] <= EARLIER ( 'Cumulated Table Finished Onboardings ECOM'[registrationTime])))

This used to work nicely. The graphs have the following measure from the main file the added table fetches data from:

All Time Top Nodes Ecom = 
CALCULATE(
    DISTINCTCOUNT('Weekly Sales Ecom'[All Topnodes]),
        FILTER(ALL('Calendar'),'Calendar'[Date]<=max('Calendar'[Date])
    )
)

This line is the dark green line which does not disappear. Note that December and January have the same number so it did not grow.

The last line which also misses a dot is basically a count of rows.

Now it seems that some values are the same as the previous month and i was thinking if this could be an issue with the setup?

I cant figure out why and where this is faulty, can anyone spot it maybe?

Best of thanks

[johnt75]

As there was no new data in January SUMMARIZE will not data for January and so there will be no rows in the calculated table. You could try using the below as a measure and put that onto the chart directly

Total Onboarding finished eCommerce =
VAR MaxDate =
    MAX ( 'Date'[Date] )
RETURN
    CALCULATE (
        COUNTROWS ( 'Finished Onboarding' ),
        'Finished Onboarding'[productMapping] = "XXX"
            || 'Finished Onboarding'[productMapping] = "YYY",
        'Finished Onboarding'[registrationTime] <= MaxDate
    )

[Schwurblini]

Hi John

So it could be the issue that if there is no additional Data, it will simply not update? Im sorry im not 100% familiar with all the DAX calculations im doing 😄 It would be weird though, because there was Data, ive checked it in the source file.

Problem with your measure is, i would need to add them incrementally on top of the last month

How could i do that with the return function?

Also the missing bright green button will reappear? its a simply count row input, not even a measure and it disappeared as well

Thank you so much for your input

[johnt75]

My measure is cumulative so it will take that into account.

The reason your row count is missing is that the implicit measure which is created when you add that column to the chart will be translated into a COUNTROWS, or one of the other count-type functions. These return blank if there is no data, and so would not be plotted onto the chart.

Everything points to there being no data for January. You can use the data view to look at the table you are creating with SUMMARIZE and apply date filters to only show data for January, that might help you track down the problem

[Schwurblini]

Hi John 

Thanks for the advice

So i checked what you said, if there is Data for January, and there is, normally added like all the months before:

I tried to create a new Data Model and there it showed up normal.

After checking, i deleted the filtering for products and this brought it back, so there was some issue with product mapping... I will keep you posted

[johnt75]

Not sure if this is causing the problem but it is best practice to not use SUMMARIZE to add calculated columns but to only use it for grouping and wrap it in ADDCOLUMNS to add the calculations. Try

Cumulated Table Finished Onboardings ECOM =
ADDCOLUMNS (
    SUMMARIZE (
        'Finished Onboarding',
        'Finished Onboarding'[Week],
        'Finished Onboarding'[registrationTime]
    ),
    "Total Onboardings", [Total Onboarding finished eCommerce]
)

SQLBI have an article about it https://www.sqlbi.com/articles/best-practices-using-summarize-and-addcolumns/ 

[Schwurblini]

Hi John

I tried it but unfortunately it did not do the trick, works just fine the same though, thanks for the input