Forum Discussion

unnijoy's avatar
unnijoy
Post Prodigy
6 years ago

HC calculation

Hi amitchandak    Country Qtr Emp HC India Qtr 1 2019 Sam 234 India Qtr 1 2019 Raj 234 India Qtr 1 2019 kit 234 India Qtr 1 2019 mas 234 India Qtr 1 2019 den 234...
Need Help
v-gizhi-msft's avatar
v-gizhi-msft
Community Support
6 years ago

Hi,

 

Please try to create two columns first:

 

Year = RIGHT(RIGHT('Table'[Qtr],6),4)
Quarter = LEFT(RIGHT('Table'[Qtr],6),1)

 

Then try this measure:

 

Measure = 
SWITCH (
    TRUE,
    MAX ( 'Table'[Qtr] ) = SELECTEDVALUE ( 'Slicer Table'[Qtr] ), 1,
    DISTINCTCOUNT ( 'Slicer Table'[Qtr] )
        = CALCULATE ( COUNTROWS ( 'Slicer Table' ), ALL ( 'Slicer Table' ) ), IF (
        MAX ( 'Table'[Quarter] ) = MAXX ( ALL ( 'Table' ), 'Table'[Quarter] )
            && MAX ( 'Table'[Year] ) = MAXX ( ALL ( 'Table' ), 'Table'[Year] ),
        1,
        0
    )
)

 

And this measure can adapt any new data by Qtr.

Hope this helps.

 

Best Regards,

Giotto

unnijoy's avatar
unnijoy
Post Prodigy
6 years ago

v-gizhi-msft  thank you very much.

 

Can you add this in the powerbi file and share it with me. so it will be very helpfull for me.

 

Waiting for your reply