Forum Discussion

vinothkumar1990's avatar
3 years ago
Solved

Cumulative data in Dax

Hi, I have 3 fileds which are Task, Planned Start Date and Planned End Date. I want calculate the cumulative count of Task based on the weekend(All Previous week+ Current week).   Raw Data: Ta...
  • Jihwan_Kim's avatar
    3 years ago

    Hi,

    I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

     

     

     

     

    Planned start date count: =
    IF (
        MIN ( 'Calendar'[Date] ) + 7
            >= MIN ( Data[Planned Start Date] )
            && MAX ( 'Calendar'[Date] ) - 7
                <= MAX ( Data[Planned End Date] ),
        CALCULATE (
            COUNTROWS (
                FILTER (
                    Data,
                    Data[Planned Start Date] <= MAX ( 'Calendar'[Date] )
                        && Data[Planned Start Date] >= MIN ( 'Calendar'[Date] )
                )
            ),
            WINDOW (
                1,
                ABS,
                0,
                REL,
                ALL ( 'Calendar'[WK enddate] ),
                ORDERBY ( 'Calendar'[WK enddate], ASC )
            )
        )
    )
    

     

    Planned end date count: = 
    IF (
        MIN ( 'Calendar'[Date] ) + 7
            >= MIN ( Data[Planned Start Date] )
            && MAX ( 'Calendar'[Date] ) - 7
                <= MAX ( Data[Planned End Date] ),
        CALCULATE (
            COUNTROWS (
                FILTER (
                    Data,
                    Data[Planned End Date] <= MAX ( 'Calendar'[Date] )
                        && Data[Planned End Date] >= MIN ( 'Calendar'[Date] )
                )
            ),
            WINDOW (
                1,
                ABS,
                0,
                REL,
                ALL ( 'Calendar'[WK enddate] ),
                ORDERBY ( 'Calendar'[WK enddate], ASC )
            )
        )
    )