Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
JunedS
Frequent Visitor

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 slicer I will get all the dates upto that selected date using the code below. 

Average CTSP =
VAR NumOfDays = -MIN('Sample'[Snapshot Week])
VAR ReferenceDate = MAX ('Sample'[Snapshot Week])
VAR PreviousDates =
    DATESINPERIOD (
       'Previous Week'[Snapshot Week],
        ReferenceDate,
        NumOfDays,
        DAY
    )
VAR Result =
    CALCULATE (
        AVERAGE('Sample'[CTSP %]),
        REMOVEFILTERS ('Sample'[Snapshot Week]),
        KEEPFILTERS ( PreviousDates),
        USERELATIONSHIP ( 'Previous Week'[Snapshot Week], 'Sample'[Snapshot Week])
    )
RETURN Result

The output of this is like: I am getting all the dates upto that selected date which is good but I also want to group the dates in 2 weeks period such that I can see only 3 dates (I don't want to group the selected week) and average their average percentage. 

26-11-2023 30%
17-12-202355%
31-12-202375%

JunedS_0-1705578551320.png

How can we acheive this. Is there any modfication that we can do in the existing measure itself ? 

Thanks in advance.



5 REPLIES 5
JunedS
Frequent Visitor

Hi team, 
Any solutuon for the problem ?

JunedS
Frequent Visitor

Thank you so much @v-xinruzhu-msft           

JunedS_1-1705647346029.png

 

JunedS_0-1705646579508.png

We are almost there but I'am getting something different than I expected. On the top chart I'am having raw data and below is the modified one. So when selecting 07-01-2024, I think it's not grouping but filtering it maybe
So the expected output looking for is: 

26-11-2023    50%
03-12-2023   55%
24-12-2023  67.5%
07-01-2024   40%


We can see it grouped in 2 weeks bin and taken the average. 

Hi @JunedS 

I don't understand the logic of the grouping, Based on your original output, don't you want to implement a return date every 14 days?

 

Best Regards!

Yolo Zhu

Hi @v-xinruzhu-msft 
Let me clarify the logic behind the grouping. Instead of setting a return date every 14 days from the selected date, the idea is to group the dates into bins of two weeks each based on the chosen date in the slicer. This means we're organizing the dates into two-week intervals, but we're not directly grouping the selected date itself.

Imagine the user selects a date in the slicer, and let's use January 7th, 2024, as an example. With the grouping logic based on two-week bins, it might look like this:

  • Group 1: January 7th 2024 (Selected date) 40%
  • Group 2: December 24th 2023 - December 31st 2023 , the average CTSP 67.5%
  • Group 3: December 3rd 2023 - December 17th 2023, the average CTSP 55%
  • Group 4: 26th Nov 2023, this is the last one left out so only one in the group with CTSP 50%

    In this case, the selected date (January 7th) falls within its own group, and the surrounding two-week periods are organized accordingly. 


v-xinruzhu-msft
Community Support
Community Support

 

 

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.

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors