Forum Discussion

Austen229022's avatar
Austen229022
Icon for Helper II rankHelper II
2 years ago
Solved

Calculate Calendar Weeks using Union & ADDCOLUMN

I have a formula that works well to use in a slicer so that I can select certain date ranges like Week, Month & Year.

 

Using DATEMTD and DATEYTD work well though I am needing to do something similar for WTD though the only way I can do it is so that I use DATESINPERIOD to get 7 days from today's date.

 

Here is the formula below:

Date Periods =
UNION(
    ADDCOLUMNS(DATESINPERIOD(Applications[Date Filter],MAX(Applications[Date Filter]),-7,DAY), "Type", "Week","Order",1),
    ADDCOLUMNS(DATESMTD(Applications[Date Filter]), "Type", "Month","Order",2),
    ADDCOLUMNS(DATESYTD(Applications[Date Filter]), "Type", "Year","Order",3)
    )
Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Austen229022 ,

     

    Hope everything is going well.

     

    We regret to inform you that there is currently no function that displays data based on the week Today is in.

     

    Alternatives are:

     

    Date Periods = 
UNION(
    ADDCOLUMNS(DATESBETWEEN(Sales[Datekey], TODAY()-7, TODAY()),"Type", "Week","Order",1),
    ADDCOLUMNS(DATESMTD(Sales[Datekey]), "Type", "Month","Order",2),
    ADDCOLUMNS(DATESYTD(Sales[Datekey]), "Type", "Year","Order",3)
    )

     

     

    Drag the fields of the table into the report page for display. The page effect is as follows:

     

    It displays the date from the previous seven days to Today calculated based on Today.

     

    If you want to know more about, please click on the following document.

    DATESBETWEEN function (DAX) - DAX | Microsoft Learn

     

    Pbix file is attached.

     

    If there is anything else you need my help with please feel free to contact me.

     

    Best Regards,
    Yang
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

3 Replies

    • Austen229022's avatar
      Austen229022
      Icon for Helper II rankHelper II
      2 years ago

      I am wanting to have it so that it shows the calendar week - same as MTD and YTD, instead of 7 days from today

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Austen229022 ,

     

    Hope everything is going well.

     

    We regret to inform you that there is currently no function that displays data based on the week Today is in.

     

    Alternatives are:

     

    Date Periods = 
UNION(
    ADDCOLUMNS(DATESBETWEEN(Sales[Datekey], TODAY()-7, TODAY()),"Type", "Week","Order",1),
    ADDCOLUMNS(DATESMTD(Sales[Datekey]), "Type", "Month","Order",2),
    ADDCOLUMNS(DATESYTD(Sales[Datekey]), "Type", "Year","Order",3)
    )

     

     

    Drag the fields of the table into the report page for display. The page effect is as follows:

     

    It displays the date from the previous seven days to Today calculated based on Today.

     

    If you want to know more about, please click on the following document.

    DATESBETWEEN function (DAX) - DAX | Microsoft Learn

     

    Pbix file is attached.

     

    If there is anything else you need my help with please feel free to contact me.

     

    Best Regards,
    Yang
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!