Measure includes all date

[MagikJukas]

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?

[MagikJukas · Accepted 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])

[johnt75]

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
    )

[MagikJukas]

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

[johnt75]

can you share some sample data which shows the problem ?

[MagikJukas]

Hi @johnt75 ,

here you have it: https://drive.google.com/file/d/16YS1Ky88TSFwG8RNNNebial71zL0cw2I/view?usp=sharing

Please be aware of:

• I filtered out 2018
• I selected categories 12000 amd 12100
• first chart is your formula: you can see the first days are way too low, because they do not read 2018 data. this is not good. (as soon you add 2018, the chart looks correct).
• second chart is my measure: you can see Jan 26 there is a drop. this does not make sense.

Ideal solution should be the second chart without those drops.

thanks