Cannot show value for previous month if current month is blank or has no data

[Anonymous]

Hi,

I work with data wherein I need to see performance of a policy for this year compared to performance of policy previous year. The policy numbers will be different for each year and hence have created a mapping of previous year policy. 

Issue I am facing is that when I select any existing policy, for eg it will have data for 4 months. But for previous policy it will have data for all 12 months. In the graph, when I put both measures, it shows data for only 4 months for both the policies. Ideally it should show for all 12 months.

I realised the issue is that for current policy data is for only 4 months and hence it is showing only 4 months for previous year as well. Due to technical challenges, I am not able to use Date table and will have to use month from data only. I tried adding +0, but didn't work.

Below are the 2 measures for selected year vs previous year.

selected_year = CALCULATE(DISTINCTCOUNT(claims_data[upload_id]),
FILTER(claims_data, claims_data'[status] = "Settled" ))

prev_year_value =

var prev_policy = MAX(claims_data[prev_policyno])

var ly_value = CALCULATE(DISTINCTCOUNT(claims_data[upload_id]),
claims_data[policyno] = prev_policy &&

 claims_data[status] = "Settled")

return
prev_year_value

As can be seen from the below 2 charts, chart on top shows data for only 4 months, whereas for previous policy there exists data for 12 months.

Regards,

Amit

[Anonymous · Accepted solution]

Hi @Anonymous ,

I suggest you to create a Calendar table to help calculation.

Calendar = ADDCOLUMNS(CALENDARAUTO() ,"Year",YEAR([Date]),"Month",FORMAT([Date],"MMMM"),"MonthSort",MONTH([Date]))

I think you can sort [Month] column by [MonthSort] column.

For reference: Sort one column by another column in Power BI

Data model:

Measure:

Selected_Year =
CALCULATE (
    DISTINCTCOUNT ( claims_data[upload_id] ),
    FILTER ( claims_data, claims_data[Status] = "Settled" )
)

Prev_Year_Value = 
CALCULATE (
    [Selected_Year],
    FILTER (
        ALL ( 'Calendar' ),
        'Calendar'[Year]
            = MAX ( 'Calendar'[Year] ) - 1
            && 'Calendar'[Month] = MAX ( 'Calendar'[Month] )
    )
)

Result in my sample is as below.

Best Regards,
Rico Zhou

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