Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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" )
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
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" )
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
Cheers
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |