topic Re: Calculating continuous MTD when there is no report for a month in DAX Commands and Tips

Calculating continuous MTD when there is no report for a month

tkaste63 — Thu, 01 Aug 2024 13:05:44 GMT

Relativley new to Power BI.

I used the quick measure to create a MTD premium

MTD Net Specific Premium =

CALCULATE(

SUM('Spec Prem'[Specific Net Premium]),

FILTER(

ALLSELECTED('Extract Date'[Extract Date]),

ISONORAFTER('Extract Date'[Extract Date], MAX('Extract Date'[Extract Date]), DESC)

)

Works great except when there is no Extract generated for a month as there was no premium paid so a report( Extract) is not generated for that month

How can I get 1/1/24 to show the vaule in 12/1/23 and show the value as of 4/1/24 in both 5/1/24 and 6/1/24. Case reserves can also create the same issue but in the instance shown is does not.

Re: Calculating continuous MTD when there is no report for a month

BeaBF — Thu, 01 Aug 2024 13:49:24 GMT

tkaste63 Hi!

1. Create a new Date table in Power BI if you don't already have one.

DateTable =
ADDCOLUMNS (
CALENDAR (DATE(2020, 1, 1), DATE(2024, 12, 31)),
"Year", YEAR([Date]),
"MonthNumber", MONTH([Date]),
"MonthName", FORMAT([Date], "MMMM"),
"YearMonth", FORMAT([Date], "YYYYMM")
)

2. Create your MTD Measure as:

MTD Net Specific Premium =
CALCULATE(
SUM('Spec Prem'[Specific Net Premium]),
FILTER(
ALL('DateTable'),
'DateTable'[Date] <= MAX('DateTable'[Date])
),
DATESMTD('DateTable'[Date])
)

Make sure your Spec Prem table has a relationship with your new DateTable based on the appropriate date columns.

BBF

Re: Calculating continuous MTD when there is no report for a month

tkaste63 — Thu, 01 Aug 2024 20:56:20 GMT

Thanks for the advise. Turns out it was a relationship issue, Spec Prem Table to Extract Date(datetable) was set to both and not single. Made the changes in the DAX formula and changed relationship to single and works great