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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ChrisCam
Frequent Visitor

Table to show last N periods without dates

Hi, I hope you can help.

 

Our company works in Periods (not months/dates) and produces data based on the following:

Period....this is based on Period number (not month) and year.

Period No.    Chronological order

1017             1

1117             2

1217             3

118               4

218              5

318              6

418              7

etc.

 

The data table shows information as follows:

Period      Contract      Spend

1117         1a               2964

1117         2a               1192

1117         3a               1640

1217         1a               2357

1217         2a               357

1217         3a               4100

118           1a               4864

118           2a               784

118           3a               54 

218           1a               1728

218           2a               1714

218           3a               864

 

Ideally I want to produce a table/matrix that, if I select from various slicers, a Contract No e.g. 1a, set a period number e.g. 218, and then select a N which is the number of periods to look back e.g. 3, the visual would show as follows:

 

Period     Spend

218         1728

118         4864

1217       2357

Total       8949

 

Due to the fact that the periods in the system to not follow a natural increase in number when moving to a new year, I am struggling to work out how I can get the table to do this. In other BI solutions I have been able to create a crosstab/matrix where I can enter a Where clause, however Power BI does not have this functionality.

 

I have seen various solutions for when a Date is used, however there is no date in the system, so I can't use this logic.

 

Any assistance would be really appreciated.

 

Many thanks

1 ACCEPTED SOLUTION
LivioLanzo
Solution Sage
Solution Sage

 

Hi @ChrisCam 

 

you ned to build the below model, then use the table PeriodSlicer for your slice and the Period for the rows in the matrix and use this measure:

 

Measure = 
IF(
    COUNTROWS( PeriodsSlicer ) = 1,
    VAR SelectedPeriod = SELECTEDVALUE(PeriodsSlicer[Chronological order])
    RETURN
        CALCULATE( 
            SUM( data[Spend] ),
            Periods[Chronological order] <= SelectedPeriod,
            Periods[Chronological order] >= SelectedPeriod - 2
        ),
    "Please select just one period"
)

 

2019-02-15_15-19-34.jpg

 


 


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


Proud to be a Datanaut!  

View solution in original post

2 REPLIES 2
LivioLanzo
Solution Sage
Solution Sage

 

Hi @ChrisCam 

 

you ned to build the below model, then use the table PeriodSlicer for your slice and the Period for the rows in the matrix and use this measure:

 

Measure = 
IF(
    COUNTROWS( PeriodsSlicer ) = 1,
    VAR SelectedPeriod = SELECTEDVALUE(PeriodsSlicer[Chronological order])
    RETURN
        CALCULATE( 
            SUM( data[Spend] ),
            Periods[Chronological order] <= SelectedPeriod,
            Periods[Chronological order] >= SelectedPeriod - 2
        ),
    "Please select just one period"
)

 

2019-02-15_15-19-34.jpg

 


 


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


Proud to be a Datanaut!  

Thank you so much for this,

It didn't give me the full solution where I could add another variable to give me the N months I want to look at, however I can cope with setting the figure to 6 and then applying a filter to show only where the measure shows and is not blank.

FYI I didn't need to separate out another column with the Contracts only.

Fantastic to get such a quick response and for it to work Smiley Very Happy

Cheers

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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