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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
svttm94
Regular Visitor

Periodically aggregated rolling calculation

Hi All,

 

I'm working on a visulisation like this:

picture 1_1.PNG

 

The last column in the graph above (in the current report) shows the December 2023 column for 3 months aggregated from October to December, but I would like to display only December , i.e. the last selected month and every three months (September, June..) from then onwards in the visualisation. I would like to display only the months from the last date selected in the year& month slicer backwards according to the period length, like this:

picture 1_v2.PNG

In the same way, if I filter to a 4 month period, then from the last month backwards, only display the dates every four months in the visualization. The report has a date range filter for the last 13 months on the filter pane.
The rolling period lengths were created using time intelligence calculations. Example of period length calculation for two and three months:

CALCULATE( SELECTEDMEASURE(), DATESINPERIOD('DateTable'[Date], LASTNONBLANK('DateTable'[Date], SELECTEDMEASURE()), -2, MONTH) )
CALCULATE( SELECTEDMEASURE(), DATESINPERIOD('DateTable'[Date], LASTNONBLANK('DateTable'[Date], SELECTEDMEASURE()), -3, MONTH) )

I have a fact table and a date dimension table like this:

picture 3.PNG

Any help would be greatly appreciated and I look forward to your feedback as soon as possible.

 

Kind regards

2 ACCEPTED SOLUTIONS
v-huijiey-msft
Community Support
Community Support

Hi @svttm94 ,

 

I created sample data as shown in the table below:

Date

Sales

1/1/2023

100

2/1/2023

200

3/1/2023

150

4/1/2023

250

5/1/2023

200

6/1/2023

450

7/1/2023

400

8/1/2023

300

9/1/2023

350

10/1/2023

600

11/1/2023

650

12/1/2023

750

1/1/2024

700

 

An index column starting from 1 is created in Power Query.

vhuijieymsft_0-1713428469060.png

 

Created a calculated column for year and month:

Year & Month = YEAR('Table'[Date]) & " " & 'Table'[Date].[Month]

 

A new table is created containing 1-12.

vhuijieymsft_1-1713428469061.png

 

Do not establish a relationship between the two tables.

 

Put "Year and Month" and "Period" into the slicer.

 

Create two measures:

Measure = MAX('Table (2)'[period])

Measure2 = 
IF (
    ROUND (
        DATEDIFF (
            MAX ( 'Table'[Date] ),
            MAXX ( ALLSELECTED ( 'Table' ), 'Table'[Date] ),
            MONTH
        ) / [Measure],
        2
    )
        - ROUND (
            DATEDIFF (
                MAX ( 'Table'[Date] ),
                MAXX ( ALLSELECTED ( 'Table' ), 'Table'[Date] ),
                MONTH
            ) / [Measure],
            0
        ) = 0,
    1
)

 

Using the "Clusterd column chart" visual, drag the fields into position.

vhuijieymsft_2-1713428521954.png

 

The Index column is for better sorting.

vhuijieymsft_3-1713428521956.png

 

Drag Measure2 into Filter and set the filter condition to is 1.

vhuijieymsft_4-1713428535943.png

 

Select the year and month from March to December 2023. The visual effect of selecting Period 3 is as follows:

vhuijieymsft_5-1713428535947.png

 

Select the year and month from March to December 2023, and the visual effect of selecting Period 4 is as follows:

vhuijieymsft_6-1713428556930.png

 

The pbix file has been attached, if you have any other questions please feel free to contact me.

 

If this method does not solve your needs, maybe you can try uploading your pbix file to me. Please be careful not to log in to your account in Power BI Desktop when uploading the pbix file.

 

Best Regards,
Yang
Community Support Team

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

View solution in original post

svttm94
Regular Visitor

Hi @v-huijiey-msft 

 

Sorry for the late reply. I could use your solution, thank you, but I had to modify my measure calculation.
I had to move the time intelligence calculation items created in the tabular editor to a measure and refer to Table (2)'[period] as follows:

 

Market Share (UC)  = CALCULATE(
    [Market Share (UC)],
    DATESINPERIOD('DateTable'[Date], LASTNONBLANK('DateTable'[Date],  [Market Share (UC)]), - MAX('Table (2)'[period]), MONTH)
)
 
Using this calculation and your Measure and Measure 2 calculations, the filtering of the visualization by period now works correctly.

Thanks again for your help!

View solution in original post

2 REPLIES 2
svttm94
Regular Visitor

Hi @v-huijiey-msft 

 

Sorry for the late reply. I could use your solution, thank you, but I had to modify my measure calculation.
I had to move the time intelligence calculation items created in the tabular editor to a measure and refer to Table (2)'[period] as follows:

 

Market Share (UC)  = CALCULATE(
    [Market Share (UC)],
    DATESINPERIOD('DateTable'[Date], LASTNONBLANK('DateTable'[Date],  [Market Share (UC)]), - MAX('Table (2)'[period]), MONTH)
)
 
Using this calculation and your Measure and Measure 2 calculations, the filtering of the visualization by period now works correctly.

Thanks again for your help!

v-huijiey-msft
Community Support
Community Support

Hi @svttm94 ,

 

I created sample data as shown in the table below:

Date

Sales

1/1/2023

100

2/1/2023

200

3/1/2023

150

4/1/2023

250

5/1/2023

200

6/1/2023

450

7/1/2023

400

8/1/2023

300

9/1/2023

350

10/1/2023

600

11/1/2023

650

12/1/2023

750

1/1/2024

700

 

An index column starting from 1 is created in Power Query.

vhuijieymsft_0-1713428469060.png

 

Created a calculated column for year and month:

Year & Month = YEAR('Table'[Date]) & " " & 'Table'[Date].[Month]

 

A new table is created containing 1-12.

vhuijieymsft_1-1713428469061.png

 

Do not establish a relationship between the two tables.

 

Put "Year and Month" and "Period" into the slicer.

 

Create two measures:

Measure = MAX('Table (2)'[period])

Measure2 = 
IF (
    ROUND (
        DATEDIFF (
            MAX ( 'Table'[Date] ),
            MAXX ( ALLSELECTED ( 'Table' ), 'Table'[Date] ),
            MONTH
        ) / [Measure],
        2
    )
        - ROUND (
            DATEDIFF (
                MAX ( 'Table'[Date] ),
                MAXX ( ALLSELECTED ( 'Table' ), 'Table'[Date] ),
                MONTH
            ) / [Measure],
            0
        ) = 0,
    1
)

 

Using the "Clusterd column chart" visual, drag the fields into position.

vhuijieymsft_2-1713428521954.png

 

The Index column is for better sorting.

vhuijieymsft_3-1713428521956.png

 

Drag Measure2 into Filter and set the filter condition to is 1.

vhuijieymsft_4-1713428535943.png

 

Select the year and month from March to December 2023. The visual effect of selecting Period 3 is as follows:

vhuijieymsft_5-1713428535947.png

 

Select the year and month from March to December 2023, and the visual effect of selecting Period 4 is as follows:

vhuijieymsft_6-1713428556930.png

 

The pbix file has been attached, if you have any other questions please feel free to contact me.

 

If this method does not solve your needs, maybe you can try uploading your pbix file to me. Please be careful not to log in to your account in Power BI Desktop when uploading the pbix file.

 

Best Regards,
Yang
Community Support Team

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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