cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

New Member

## Calculating continuous MTD when there is no report for a month

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.

1 ACCEPTED SOLUTION
Memorable Member

@tkaste63 Hi!

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

DateTable =
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

2 REPLIES 2
New Member

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

Memorable Member

@tkaste63 Hi!

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

DateTable =
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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.