Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jackandrew
Frequent Visitor

Measures derived from date ranges

I have a row for each policy period, which at most is an entire year. Each row has a date range, the policy period, and the % of 12 months, what I'm calling the exposure. So 1 year is 1, and 6 months is 0.5 and so on. Lastly, I have the premium and losses for that policy period. 

 

Example.png

 

What I'd like to be able to do, which I'm not sure is possible, is have measures for premium, losses, and exposures sliced by date range given just the policy period date range. That is, monthly premium, daily premium, or total exposures sliced by date range given only the start_date and end_date of each row. Ideally, I'll be able to have a date slicer and see the monthly or daily totals and averages as change the date range of the slicer.

 

The only way I can think to do this is to expand each policy period row into monthly rows, weekly rows, or daily rows to get the respective average. So a 6-month policy could be expanded into 6 rows, one for each month. But since I'm dealing with millions of rows in my dataset, I would prefer not to compound its size any further.

 

Is there anyway to get measures based only off date ranges?

1 ACCEPTED SOLUTION

Hi @jackandrew,

 

Please download the demo from the attachment. 

1. Create an independent date table. 

2. Don't establish any relationships.

3. Create two measures.

4. One tip: you have to handle the date range in the slicer. The formula doesn't handle anything like monthly, daily.

Measure =
VAR rangeStart =
    MIN ( 'Calendar'[Date] )
VAR rangeEnd =
    MAX ( 'Calendar'[Date] )
RETURN
    SUMX (
        SUMMARIZE (
            'Table1',
            Table1[policy],
            Table1[start_date],
            Table1[end_date],
            "rowPremium",
            VAR rangeDays =
                DATEDIFF ( [start_date], [end_date], DAY )
            VAR inrangeDays =
                IF (
                    Table1[start_date] >= rangeStart
                        && rangeEnd >= Table1[start_date]
                        && rangeEnd <= Table1[end_date],
                    DATEDIFF ( Table1[start_date], rangeEnd, DAY ),
                    IF (
                        Table1[start_date] >= rangeStart
                            && rangeEnd >= Table1[end_date],
                        DATEDIFF ( Table1[start_date], Table1[end_date], DAY ),
                        IF (
                            Table1[start_date] <= rangeStart
                                && rangeEnd <= Table1[end_date],
                            DATEDIFF ( rangeStart, rangeEnd, DAY ),
                            IF (
                                Table1[start_date] <= rangeStart
                                    && rangeEnd >= Table1[end_date]
                                    && rangeStart <= Table1[end_date],
                                DATEDIFF ( rangeStart, Table1[end_date], DAY ),
                                0
                            )
                        )
                    )
                )
            RETURN
                DIVIDE ( inrangeDays, rangeDays, 0 ) * SUM ( Table1[premium] )
        ),
        [rowPremium]
    )
Measure 2 =
VAR rangeStart =
    MIN ( 'Calendar'[Date] )
VAR rangeEnd =
    MAX ( 'Calendar'[Date] )
RETURN
    SUMX (
        SUMMARIZE (
            'Table1',
            Table1[policy],
            Table1[start_date],
            Table1[end_date],
            "rowExposure",
            VAR inrangeDays =
                IF (
                    Table1[start_date] >= rangeStart
                        && rangeEnd >= Table1[start_date]
                        && rangeEnd <= Table1[end_date],
                    DATEDIFF ( Table1[start_date], rangeEnd, DAY ),
                    IF (
                        Table1[start_date] >= rangeStart
                            && rangeEnd >= Table1[end_date],
                        DATEDIFF ( Table1[start_date], Table1[end_date], DAY ),
                        IF (
                            Table1[start_date] <= rangeStart
                                && rangeEnd <= Table1[end_date],
                            DATEDIFF ( rangeStart, rangeEnd, DAY ),
                            IF (
                                Table1[start_date] <= rangeStart
                                    && rangeEnd >= Table1[end_date]
                                    && rangeStart <= Table1[end_date],
                                DATEDIFF ( rangeStart, Table1[end_date], DAY ),
                                0
                            )
                        )
                    )
                )
            RETURN
                DIVIDE ( inrangeDays, 365, 0 )
        ),
        [rowExposure]
    )

Measures-derived-from-date-ranges

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

View solution in original post

11 REPLIES 11
AlB
Super User
Super User

Hi @jackandrew

 

Can you provide an example to clarify what you need exactly?

For instance, if the date range in the slicer slicer has, say, October 2014, what would your measures yield and why?

Thank you for the response, @AlB! A simple measure I would use is total premium and exposure, sliced by date range. So, for October 2014 I would need to calculate the October 2014 premium and exposure for each row, then sum to get the total.

 

Using the example below: the third Policy C row has 29 days in October, 2014. So the exposure is 29/365 = 0.079. The premium for that 54 day policy period is $319, but the October portion is 29 days so the October premium is $171.31. The same logic goes for the other row with October days. Summing these two rows give the total October 2014 premium and exposure.

 

Ideally, I would like this kind of measure to be more flexible than just monthly, so it could work with specific date ranges, not just monthly ranges.

 

Example 2.png

 

Thanks!

 

@jackandrew

Ouch. Yeah that looks quite tough. I hadn't at first realized the issue with filtering the dates. I guess you would need to structure the data in a different way but I don't think I can be of much help there. I'm curious though, how would you expand the policy periods? With DAX? Something else?    

@jackandrew

 

Something came to mind but it will be potentially problematic in terms of memory and CPU consumption, given the large size of your data.

Let's break the problem up.

I think the best way would be to have an extra column in the table that tells you how many days in the period of the policy fall within the range selected. This could be done with something like (Table is your table shown above):

 

DaysInSelectedRange =
COUNTROWS (
    INTERSECT (
        DATESBETWEEN ( 'Date'[Date]; Table[Start_date]; Table[End_date] );
        VALUES ( 'Date'[Date] )
    )
)

 

 Now the problem is that that column has to be generated dynamically when the date range is selected. We could have something like: 

DynamicTable =
ADDCOLUMNS (
    Table;
    "DaysInSelectedRange"COUNTROWS (
        INTERSECT (
            DATESBETWEEN ( 'Date'[Date]; Table[Start_date]; Table[End_date] );
            VALUES ( 'Date'[Date] )
        )
    )
)

You can see that the memory and CPU requirements escalate scarily and rapidly. But let's push ahead, we can worry about performance later.

With the number of days of the policy within the range calculated, we can determine the corresponding Premium and Exposure in two additional columns, as you just explained. Then you can wrap the table in a SUMX to add up Premium. I will not go into the details of calculating the Premium and Exposure columns but it would look like this:

 

SUMX (
    ADDCOLUMNS (
        ADDCOLUMNS (
            Table;
            "DaysInSelectedRange"; COUNTROWS (
                INTERSECT (
                    DATESBETWEEN ( 'Date'[Date]; Table[Start_date]; Table[End_date] );
                    VALUES ( 'Date'[Date] )
                )
            )
        );
        "Premium"; [OperationsForPremium];
        "Exposure"; [OperationsForExposure]
    );
    [Premium]
)

Bear in mind that I just wrote this without checks and there might be errors but it is just to get the general idea.

There are a lot of things in that code that are expensive from a performance point of view so that is more than likely to be an issue

Mostly because we are not fully using the power of DAX with, for instance, filtering.

What do you think?

 

 

@AlB Sorry my I'm still a beginner at DAX. I was able to follow you for DaysInSelectedRange to calculate the numbers of days, but the second formula DynamicTable fails to the error "The expressions refers to multiple columns. Multiple columns cannot be converted to a scalar value". 

@jackandrew

Ok, if you are a beginner this might be a bit tough to follow. DynamicTable generates a table and I was just showing it as an intermediate step that will be part of the final measure. You can create a new table with that code in Power BI. I would just concentrate in the logic for building the measure though.  

Can you share a sample of your data? Then I could refine the code for potential errors when I find some time. 

@AlB Sure - what's the best the way to share a sample dataset here? 

 

In response to a previous question you had - If I was to expand each row in my dataset to rows for months or days, I would use the query editor in Power BI. This discussion helped me figure out how to do that:

 

https://community.powerbi.com/t5/Desktop/How-to-convert-show-monthly-margin-from-single-row-of-data-...

 

Having a row for each day or month in the policy period is my backup plan. I am hoping it's possible to have flexibility with date ranges without hitting the row limit!

@jackandrew

 

You can just post here the URL for your file from platforms like Dropbox, Onedrive, etc.  or use some website like this

Hi @jackandrew,

 

Please download the demo from the attachment. 

1. Create an independent date table. 

2. Don't establish any relationships.

3. Create two measures.

4. One tip: you have to handle the date range in the slicer. The formula doesn't handle anything like monthly, daily.

Measure =
VAR rangeStart =
    MIN ( 'Calendar'[Date] )
VAR rangeEnd =
    MAX ( 'Calendar'[Date] )
RETURN
    SUMX (
        SUMMARIZE (
            'Table1',
            Table1[policy],
            Table1[start_date],
            Table1[end_date],
            "rowPremium",
            VAR rangeDays =
                DATEDIFF ( [start_date], [end_date], DAY )
            VAR inrangeDays =
                IF (
                    Table1[start_date] >= rangeStart
                        && rangeEnd >= Table1[start_date]
                        && rangeEnd <= Table1[end_date],
                    DATEDIFF ( Table1[start_date], rangeEnd, DAY ),
                    IF (
                        Table1[start_date] >= rangeStart
                            && rangeEnd >= Table1[end_date],
                        DATEDIFF ( Table1[start_date], Table1[end_date], DAY ),
                        IF (
                            Table1[start_date] <= rangeStart
                                && rangeEnd <= Table1[end_date],
                            DATEDIFF ( rangeStart, rangeEnd, DAY ),
                            IF (
                                Table1[start_date] <= rangeStart
                                    && rangeEnd >= Table1[end_date]
                                    && rangeStart <= Table1[end_date],
                                DATEDIFF ( rangeStart, Table1[end_date], DAY ),
                                0
                            )
                        )
                    )
                )
            RETURN
                DIVIDE ( inrangeDays, rangeDays, 0 ) * SUM ( Table1[premium] )
        ),
        [rowPremium]
    )
Measure 2 =
VAR rangeStart =
    MIN ( 'Calendar'[Date] )
VAR rangeEnd =
    MAX ( 'Calendar'[Date] )
RETURN
    SUMX (
        SUMMARIZE (
            'Table1',
            Table1[policy],
            Table1[start_date],
            Table1[end_date],
            "rowExposure",
            VAR inrangeDays =
                IF (
                    Table1[start_date] >= rangeStart
                        && rangeEnd >= Table1[start_date]
                        && rangeEnd <= Table1[end_date],
                    DATEDIFF ( Table1[start_date], rangeEnd, DAY ),
                    IF (
                        Table1[start_date] >= rangeStart
                            && rangeEnd >= Table1[end_date],
                        DATEDIFF ( Table1[start_date], Table1[end_date], DAY ),
                        IF (
                            Table1[start_date] <= rangeStart
                                && rangeEnd <= Table1[end_date],
                            DATEDIFF ( rangeStart, rangeEnd, DAY ),
                            IF (
                                Table1[start_date] <= rangeStart
                                    && rangeEnd >= Table1[end_date]
                                    && rangeStart <= Table1[end_date],
                                DATEDIFF ( rangeStart, Table1[end_date], DAY ),
                                0
                            )
                        )
                    )
                )
            RETURN
                DIVIDE ( inrangeDays, 365, 0 )
        ),
        [rowExposure]
    )

Measures-derived-from-date-ranges

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

@v-jiascu-msft

 

That did the trick! Thank you!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.