Forum Discussion

JunedS's avatar
JunedS
Frequent Visitor
2 years ago

Dynamic Grouping of Dates

Hi community,  I have a simple table which include only dates (start of the week) and respective percentages. I have already done the dynamic filtering of dates so that if I select any date from the...
Need Help
Show and Tell
Tips and Tricks
Anonymous's avatar
Anonymous
Not applicable
2 years ago

 

 

Hi JunedS 

You can try the following measure.

Average CTSP =
VAR NumOfDays =
    - MIN ( 'Sample'[Snapshot Week] )
VAR ReferenceDate =
    MAXX ( ALLSELECTED ( 'Sample' ), [Snapshot Week] )
VAR PreviousMinDate =
    CALCULATE (
        MIN ( 'Previous Week'[Snapshot Week] ),
        DATESINPERIOD ( 'Previous Week'[Snapshot Week], ReferenceDate, NumOfDays, DAY )
    )
VAR Generatedate =
    GENERATESERIES ( PreviousMinDate, ReferenceDate, 14 )
VAR Result =
    CALCULATE (
        AVERAGE ( 'Sample'[CTSP %] ),
        REMOVEFILTERS ( 'Sample'[Snapshot Week] ),
        FILTER ( 'Previous Week', [Snapshot Week] IN Generatedate ),
        USERELATIONSHIP ( 'Previous Week'[Snapshot Week], 'Sample'[Snapshot Week] )
    )
RETURN
    Result

Best Regards!

Yolo Zhu

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