Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi friends,
Need some quick help.
In Table A I have col - PERIOD LOOKUP
In Table B I have 2 cols - Year & Period Short and CCI_Num
I want to count the CCI_Num but cumulatively for last 13 months ( Here I have the date col in Whole number datatype and we should not change this to date datatype as per client)
When I select the PERIOD LOOKUP value in the slicer for example - 201901 the I should get the cumulative count of the CCI_Num for the last 13 months i.e upto - 201712 (Difference between 201901 and 201712 is 13 months)
I am getting the result but when I select the value in the slicer I am not getting the last 13 months in the visual.
I used the formula - 13period_ =
var P = SELECTEDVALUE(CCS[Year & Period Short])
return
CALCULATE([ccicount],FILTER(ALL(CCS), CCS[Year & Period Short]>=P-12 && CCS[Year & Period Short]<=P))
I tried the disconnected table concept but unable to get the result.
Request to help.
Hi @kumar_powerbi ,
According to your description, you want to get the cumulative count of the CCI_Num for the last 13 months by the PERIOD LOOKUP value, but in your formula, your column referenced in the selectedvalue isn't PERIOD LOOKUP column.
I modify the formula and get this result.
Because the two tables are not related, all the date will get the same cumulative value based on the selected date in the slicer.
I gusee this is not your expected result, but I'm not very clear what's your expected result.
X-axis: display only the 13 previous month ?
Y-axis: aren't all values the same as my snapshot, if not, what's the result. For example, the slicer select 201901, for the date 201901, it should be cumulative from 201712 to 201901, then what's the calculation for 201812.
Could you please explain more about it?
Best Regards,
Community Support Team _ kalyj
Try removing the FILTER call
13period_ =
VAR P =
SELECTEDVALUE ( CCS[Year & Period Short] )
RETURN
CALCULATE (
[ccicount],
ALL ( CCS ),
CCS[Year & Period Short] >= P - 12
&& CCS[Year & Period Short] <= P
)
If we remove filter ALL it not calculating cumulative and its only cumulating only with in the year,
it will not considering next year..,
For Ex : if i select 202203 the vlues showing past 13 months for cumulative start from slected values..,
its cumulating only 202113 for last 13 period next starting 202212 not cumulating the value..
its considering only with in the period only..
I want like rolling cumulative of 13 period values as its start's from selected period values in slicer
The ALL is still included in my code, it is the call to the FILTER function which I suggest removing. You don't need that in parameters which you pass to CALCULATE.
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |