Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi,
I am trying to build a rolling 28 days measure:
CALCULATE (
SUM ( OrderEntry[Total] ),
FILTER(
ALLSELECTED(OrderEntry),
(OrderEntry[Created on])>MAX(OrderEntry[Created on])-28 && OrderEntry[Created on]<=MAX(OrderEntry[Created on])))
the problem with this measure is that, as I am excluding years earlier than 2019, the measure is not computing 2018 numbers for the first days of 2019.
If I replace ALLSELECTED(OrderEntry) with ALLSELECTED(OrderEntry[Created on]) the measure does not compute anymore the last 28 days.
any advice?
Solved! Go to Solution.
So, the only solution I found to fix it, is to create a new table containing all dates values.
Then I use it in my chart. this solve the issue, but I am still persuaded it can be solved my reworking the measure.
Hopefully someone will figure this out.
OrderEntry Calendar = VALUES(OrderEntry[Date2])
So, the only solution I found to fix it, is to create a new table containing all dates values.
Then I use it in my chart. this solve the issue, but I am still persuaded it can be solved my reworking the measure.
Hopefully someone will figure this out.
OrderEntry Calendar = VALUES(OrderEntry[Date2])
Try
Rolling 28 days =
VAR MaxDate =
MAX ( OrderEntry[Created on] )
RETURN
CALCULATE (
SUM ( OrderEntry[Total] ),
ALLSELECTED ( OrderEntry ),
REMOVEFILTERS ( OrderEntry[Created on] ),
OrderEntry[Created on] > MaxDate - 28
&& OrderEntry[Created on] <= MaxDate
)
thanks @johnt75, that works, until I start using a slicer.
see what happen when I select two elemets in my slicer "Category": the data shows heavy pitfalls.
this because for few given dates, the data is empty for one of the category selected.
with the other formula this does not happen, however it give wrong data for the first days of the starting year.
thanks
can you share some sample data which shows the problem ?
Hi @johnt75 ,
here you have it: https://drive.google.com/file/d/16YS1Ky88TSFwG8RNNNebial71zL0cw2I/view?usp=sharing
Please be aware of:
Ideal solution should be the second chart without those drops.
thanks
User | Count |
---|---|
52 | |
48 | |
20 | |
16 | |
15 |
User | Count |
---|---|
109 | |
45 | |
42 | |
24 | |
20 |