Summarized Table but all date periods

[StidifordN]

Hi all,

I have a new table being created and all is working correctly - except when - there is no data for a month period in the Driver Behaviour data but there is an infringement dated within that period.   In this scenario, the table does not display a row for the month, and therefor doesn't lookup the infringement that existed in the period.  In this scenario it is Nov 2021.

Can the below be adjusted to show ALL month periods -  either

• between the earliest and latest that exist in the Driver Behaviour table, or
• between the earliest and latest that exist in the Dates table, or
• between Jan 1 2019 and the end of the previous month??

Driver Scoring Draft = 
SUMMARIZE(
    DriverBehaviour,
    DriverBehaviour[Driver],
    Dates[Date (bins) Month Period],
    "Total Style Score",
        CALCULATE(SUM(DriverBehaviour[Driver Style Points])),
    "Total KM Travelled",
        CALCULATE(SUM(DriverBehaviour[Trip Distance Final])),
    "Infringements Count",
        CALCULATE(COUNTROWS(FILTER(InfringementData,InfringementData[Penalty Type]="Speeding or Red light Camera")),
        RELATEDTABLE(DriverBehaviour)
        )
        )

[v-tianyich-msft]

Hi @StidifordN ,

You can choose to show items with no data, if it doesn't work, please provide a sample to better solve the problem for you.

Hope it helps!

Best regards,
Community Support Team_ Scott Chang

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

[StidifordN]

Thanks for the response @v-tianyich-msft  - but this isn't a visual as yet, this issue is in attempting to create the table data.  

[amitchandak]

@StidifordN ,
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Try if this can help

Driver Scoring Draft =
SUMMARIZE(
Dates, // Assuming 'Dates' is your calendar table
Dates[Date (bins) Month Period],
"Driver", FIRSTNONBLANK(DriverBehaviour[Driver], "Unknown"), // Replace "Unknown" with a suitable default or blank
"Total Style Score",
CALCULATE(SUM(DriverBehaviour[Driver Style Points]), DriverBehaviour),
"Total KM Travelled",
CALCULATE(SUM(DriverBehaviour[Trip Distance Final]), DriverBehaviour),
"Infringements Count",
CALCULATE(
COUNTROWS(
FILTER(
InfringementData,
InfringementData[Penalty Type] = "Speeding or Red light Camera"
)
),
RELATEDTABLE(DriverBehaviour)
)
)

[StidifordN]

Thanks for the response @amitchandak 

Whilst this did result in every month being displated regardless of data, Unfortunately it also resulted in just the first name to appear per month.  

I will create a sample pbix file and respond shortly.