Forum Discussion

jbuzaglu's avatar
jbuzaglu
Frequent Visitor
3 years ago
Solved

Calculating Elapsed QTD

Hi, I need a measure for elapsed Quarter-To-Date, for example today is March 21st, so it would be 31+28+21=80 elapsed days so far. Divided by 31+28+31=90 total days this quarter. So today the measure...
Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi jbuzaglu ,

     

    Here's my solution.

    1.Create a calendar table by dax.

    Calendar = ADDCOLUMNS(CALENDAR(DATE(2023,1,1),DATE(2023,12,31)),"Quarter",QUARTER([Date]))

     

    2.Create a measure return the percentage.

    Elapsed QTD = 
VAR _CALENDAR =
    SUMMARIZE (
        CALENDAR ( DATE ( 2023, 1, 1 ), DATE ( 2023, 12, 31 ) ),
        [Date],
        "Quarter", QUARTER ( [Date] )
    )
VAR _min =
    CALCULATE (
        MIN ( 'Calendar'[Date] ),
        FILTER ( ALL ( 'Calendar' ), [Quarter] = QUARTER ( TODAY () ) )
    )
VAR _MAX =
    CALCULATE (
        MAX ( 'Calendar'[Date] ),
        FILTER ( ALL ( 'Calendar' ), [Quarter] = QUARTER ( TODAY () ) )
    )
VAR _DAYS =
    COUNTROWS ( FILTER ( 'Calendar', [Date] >= _min && [Date] <= _MAX ) )
VAR _DAYS2 =
    COUNTROWS ( FILTER ( 'Calendar', [Date] <= TODAY () && [Date] >= _min ) )
RETURN
    DIVIDE ( _DAYS2, _DAYS )

                                                                                                                                                             

    Best Regards,

    Stephen Tao

     

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