Forum Discussion

MagikJukas's avatar
MagikJukas
Resolver III
3 years ago
Solved

Measure includes all date

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's avatar
    MagikJukas
    3 years ago

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

5 Replies

  • johnt75's avatar
    johnt75
    Super User
    3 years ago

    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's avatar
      MagikJukas
      Resolver III
      3 years ago

      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's avatar
        johnt75
        Super User
        3 years ago

        can you share some sample data which shows the problem ?

  • MagikJukas's avatar
    MagikJukas
    Resolver III
    3 years ago

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