topic Re: Measure includes all date in DAX Commands and Tips

Measure includes all date

MagikJukas — Tue, 21 Feb 2023 12:11:51 GMT

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?

Re: Measure includes all date

johnt75 — Tue, 21 Feb 2023 14:35:23 GMT

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 )

Re: Measure includes all date

MagikJukas — Tue, 21 Feb 2023 14:47:34 GMT

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

Re: Measure includes all date

johnt75 — Tue, 21 Feb 2023 15:28:53 GMT

can you share some sample data which shows the problem ?

Re: Measure includes all date

MagikJukas — Tue, 21 Feb 2023 20:18:14 GMT

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

Re: Measure includes all date

MagikJukas — Thu, 23 Feb 2023 12:58:31 GMT

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])