Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Calculating cumulative excluding certain values depending on the certain dates

Dear Friends, I hope you are all doing well. I am working on the following subject and I am having issue how to make formula which will calculate cumulative headcounts but for the certain months whe...
Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    5 years ago

    Hi  Anonymous ,

     

    Modify your measure as below:

     

    Actual HC YTD2 =
VAR _tab =
    CALCULATETABLE (
        VALUES ( 'Maternity'[Current Date] ),
        FILTER (
            ALL ( Maternity ),
            'Maternity'[Cost Center] = MAX ( 'Maternity'[Cost Center] )
                && 'Maternity'[Maternity] = "M"
        )
    )
RETURN
    CALCULATE (
        SUM ( 'HC'[qty] ),
        FILTER ( ALL ( Dates ), Dates[Date] <= MAX ( Dates[Date] ) )
    )
        - CALCULATE (
            SUM ( 'HC'[qty] ),
            FILTER ( 'HC', 'HC'[Start Date of Employment] IN _tab )
        )

     

    And you will see:

    Check the related .pbix file attached.

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my post as a solution!

v-kelly-msft's avatar
v-kelly-msft
Icon for Community Support rankCommunity Support
5 years ago

Hi  Anonymous ,

 

Modify your measure as below:

 

Actual HC YTD2 =
VAR _tab =
    CALCULATETABLE (
        VALUES ( 'Maternity'[Current Date] ),
        FILTER (
            ALL ( Maternity ),
            'Maternity'[Cost Center] = MAX ( 'Maternity'[Cost Center] )
                && 'Maternity'[Maternity] = "M"
        )
    )
RETURN
    CALCULATE (
        SUM ( 'HC'[qty] ),
        FILTER ( ALL ( Dates ), Dates[Date] <= MAX ( Dates[Date] ) )
    )
        - CALCULATE (
            SUM ( 'HC'[qty] ),
            FILTER ( 'HC', 'HC'[Start Date of Employment] IN _tab )
        )

 

And you will see:

Check the related .pbix file attached.

 

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Dear Kelly,

 

Thank you very much ! It works !