Forum Discussion

SemaD_'s avatar
SemaD_
Regular Visitor
3 years ago
Solved

Not able show dates in my visual without data

I have a line graph visual that uses my mannually created calendar table (Hierarchy) and Data from another Table to show the average sessions over the period. However, as you can see when you drill down into the graph, only dates with data are being shown. This means I am not getting accurate data cause it is not taking the average of everyday of that month. The thing is I am fine with dates with no data not showing on 99% of my other visuals but for this visual in particular it is important for that full picture. 
How can I show all dates in there even if there is no data for that given day?

My formula and followings below: 

Calendar = CALENDAR(
    DATE(YEAR(MIN('Pogo E Connect Data'[Start Date])),1,1),
    DATE(YEAR(MAX('Pogo E Connect Data'[Start Date])),12,31))

Average Number of Sessions =
AVERAGEX(
    SUMMARIZE(
        'Pogo E Connect Data',
        'Calendar'[Year],
        'Calendar'[Quarter]
    ),
    CALCULATE(
        AVERAGEX(
            CALCULATETABLE(
                SUMMARIZE(
                    'Pogo E Connect Data',
                    'Calendar'[Month],
                    'Pogo E Connect Data'[Charger Type]
                ),
                CROSSFILTER('Pogo E Connect Data'[Start Date], 'Calendar'[Date], BOTH)
            ),
            CALCULATE(
                AVERAGEX(
                    VALUES('Calendar'[Date]),
                    COALESCE([SDR ID average per CP ID], 0)
                )
            )
        )
    )
)

I have set a Many to one relationship the Start Date in the raw data table to the Calendar [Date]. If anyone could help with something I may be missing I would really appreciate that. 
Need Help
Tips and Tricks
  • SemaD_'s avatar
    SemaD_
    3 years ago

    I have found a solution DAX to show any blanks as 0!

    AverageWithoutZero =
    VAR CalculatedAverage =
    AVERAGEX(
        SUMMARIZE(
            'Pogo E Connect Data',
            'Calendar'[Year],
            'Calendar'[Quarter]
        ),
        CALCULATE(
            AVERAGEX(
                CALCULATETABLE(
                    SUMMARIZE(
                        'Pogo E Connect Data',
                        'Calendar'[Month],
                        'Pogo E Connect Data'[Charger Type]
                    ),
                    CROSSFILTER('Pogo E Connect Data'[Start Date], 'Calendar'[Date], BOTH)
                ),
                CALCULATE(
                    AVERAGEX(
                        VALUES('Calendar'[Date]),
                        COALESCE([SDR ID average per CP ID], 0)
                    )
                )
            )
        )
    )
    RETURN
    IF (
        ISBLANK(CalculatedAverage),
        0,
        CalculatedAverage
    )

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    have you tried adding 

     

    Average Number of Sessions = AVERAGEX( VALUES('Calendar'[Date]), CALCULATE( AVERAGEX( SUMMARIZE(

    • SemaD_'s avatar
      SemaD_
      Regular Visitor
      3 years ago

      I tried your suggestion but unfortunately the results of the visual still excludes dates without data😣

  • SemaD_'s avatar
    SemaD_
    Regular Visitor
    3 years ago

    I have found a solution DAX to show any blanks as 0!

    AverageWithoutZero =
    VAR CalculatedAverage =
    AVERAGEX(
        SUMMARIZE(
            'Pogo E Connect Data',
            'Calendar'[Year],
            'Calendar'[Quarter]
        ),
        CALCULATE(
            AVERAGEX(
                CALCULATETABLE(
                    SUMMARIZE(
                        'Pogo E Connect Data',
                        'Calendar'[Month],
                        'Pogo E Connect Data'[Charger Type]
                    ),
                    CROSSFILTER('Pogo E Connect Data'[Start Date], 'Calendar'[Date], BOTH)
                ),
                CALCULATE(
                    AVERAGEX(
                        VALUES('Calendar'[Date]),
                        COALESCE([SDR ID average per CP ID], 0)
                    )
                )
            )
        )
    )
    RETURN
    IF (
        ISBLANK(CalculatedAverage),
        0,
        CalculatedAverage
    )