X axis sum of matching months and year of a different column

[RES]

Report Month	Value	Incident
2019-08-01	248	2022-02-15
2020-10-01	251.67	2022-02-17
2022-01-01	150	2022-02-08
2022-01-01	200	2022-02-17
2022-01-01	200	2022-02-14
2022-01-01	19.5	2022-02-21
2022-01-01	200	2022-02-22
2022-02-21	366.6	2022-02-21

I would like to use report month on the X axis of a bar chart that sums Value of the Incident column where it is within the month.

For instance this would sum up to 1635.77 for Feb 2022 since all of these had an Incident in Feb 2022 but they were last reported on their Report Month. So the x axis will show a bar totaling 1635.77 for feb 2022 if I use Report Month. 

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure if I understood your question correctly, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

Expected result measure: = 
VAR _t =
    ADDCOLUMNS (
        CALCULATETABLE (
            data,
            REMOVEFILTERS ( 'calendar'[Year-Month sort], 'calendar'[Year-Month] )
        ),
        "@incident_end_of_month_date", EOMONTH ( data[incident_date], 0 )
    )
VAR _currentmonth =
    VALUES ( 'calendar'[Year-Month sort] )
RETURN
    SUMX (
        FILTER ( _t, [@incident_end_of_month_date] IN _currentmonth ),
        data[value]
    )

[v-dineshya · Accepted solution]

Hi @RES ,

Thanks for reaching out to the Microsoft fabric community forum.

Please follow below steps:

1. Create a calculated column (or create a new table if you prefer) to extract the Incident Year-Month:

Incident YearMonth = FORMAT('Table'[Incident], "YYYY-MM")
or
Incident YearMonthDate = DATE(YEAR('Table'[Incident]), MONTH('Table'[Incident]), 1)

2. Now create your bar chart: X-axis: Incident YearMonth and Y-axis: SUM of Value

DAX Measure:

Total Value By Incident Month =
VAR SelectedReportMonth = MAX('Table'[Report Month])
VAR MatchingIncidentValues =
CALCULATE(
SUM('Table'[Value]),
FILTER(
'Table',
YEAR('Table'[Incident]) = YEAR(SelectedReportMonth)
&& MONTH('Table'[Incident]) = MONTH(SelectedReportMonth)
)
)
RETURN
MatchingIncidentValues

Then put Report Month on the X-axis and Plot the Total Value By Incident Month measure as the Y-axis.

If you find this post helpful, please mark it as an "Accept as Solution" and consider giving a KUDOS. Feel free to reach out if you need further assistance.
Thanks and Regards

[v-dineshya]

Hi @RES ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and consider giving a KUDOS. Feel free to reach out if you need further assistance.

Regards,

Dinesh

[v-dineshya]

Hi @RES ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and consider giving a KUDOS. Feel free to reach out if you need further assistance.

Regards,

Dinesh

[v-dineshya]

Hi @RES ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and consider giving a KUDOS. Feel free to reach out if you need further assistance.

Regards,

Dinesh

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure if I understood your question correctly, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

Expected result measure: = 
VAR _t =
    ADDCOLUMNS (
        CALCULATETABLE (
            data,
            REMOVEFILTERS ( 'calendar'[Year-Month sort], 'calendar'[Year-Month] )
        ),
        "@incident_end_of_month_date", EOMONTH ( data[incident_date], 0 )
    )
VAR _currentmonth =
    VALUES ( 'calendar'[Year-Month sort] )
RETURN
    SUMX (
        FILTER ( _t, [@incident_end_of_month_date] IN _currentmonth ),
        data[value]
    )