Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Measure dealing with date

Hi,

 

I am currently working on a dashboard and was wondering if someone could help with a fomula. So a brief explanation of what I want to do is have a slicer where I can select a week on the calender, but I would like the measure to sum up all sales in the prior 3 weeks before the first day of the week I selected on the slicer. 

 

An example I could give was lets say I have apple and orange sales for a three month period. I would like to be able to impliment a slicer where I narrow down to just a 7 day period of this 3 month time frame and then have a measure that would sum all the apple and oranges sales for the three weeks prior. So lets say I select October 12- October 18, I would like a measure that finds me the sales from September 21- October 11. 

 

If someone could help with this I would be forever grateful.

4 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    1 year ago

    Hi,

    I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

    Please check the below picture and the attached pbix file.

     

     

     

     

    WINDOW function (DAX) - DAX | Microsoft Learn

     

    expected result measure: =
VAR _t =
    WINDOW (
        -3,
        REL,
        -1,
        REL,
        ALL ( 'calendar slicer'[Year-Week] ),
        ORDERBY ( 'calendar slicer'[Year-Week], ASC )
    )
RETURN
    CALCULATE (
        SUM ( sales[sales] ),
        'calendar'[Year-Week] IN _t
    )

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Hi,

       

      So I actually have dates already predeterminded ina column, does this change the way I approach this?

       

      Here is an image of my columns

       

      Thanks

      • Jihwan_Kim's avatar
        Jihwan_Kim
        Super User
        1 year ago

        Hi,

        Please share your sample pbix file's link and how the expected outcome looks like in the visualization, and then I can try to look into it.

        Thank you.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Anonymous ,

     

    I've made a test for your reference:

    1\My data sorcue(Sheet1)

    2\Create a caculate table

    Calendar = CALENDAR(DATE(2024,1,1),DATE(2024,12,31))

    3\Add a measure for sheet1

    FilterOnData = If(SELECTEDVALUE(Sheet1[Date])<=  MIN('Calendar'[Date])-1 && SELECTEDVALUE(Sheet1[Date]) >=  MIN('Calendar'[Date])-8,1,0)

    4\Add a silder

    5\Add a table and filter on the measure

     

    Best Regards,

    Bof