Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Filtering a Month based on a selection of a Week

Hi All,

 

I'm trying to create a formula where someone selects a week of the year in a slicer, and my formula takes all the dates from that week, pulls the MAX month from that week (since a week can exist in more than one month), and returns all of the "Volume" data for that entire month.

 

I have a formula like I explained above working when I use a slicer for a Date instead of a Week which I've included screenshots of below:

 

 

Notice, that I select the date 1/9/2018, and the "Volume MTD" field, gives me the values for the entire month of January.  If nothing is selected in the slicers, then I display the totals for 2 months before current month.  The formula I'm using for "Volume MTD" is the following:

 

Volume MTD = IF( ISFILTERED(Bookings[Bookings Date]),
                 CALCULATE(SUM(Bookings[Volume]), MONTH(Bookings[Bookings Date]) = MONTH(FILTERS(Bookings[Bookings Date])) ),
                 CALCULATE(SUM(Bookings[Volume]), MONTH(Bookings[Bookings Date]) = MONTH(TODAY())-2 )
)

 Using the same logic above, I want to filter for "Bookings Week" and have the entire month's total "Volume" show up in the same way that I have working by selecting a date.  When I try changing the formula above to the following:

 

Volume MTD 2 = IF( ISFILTERED(Bookings[Bookings Week]),
                 CALCULATE(SUM(Bookings[Volume]), MONTH(Bookings[Bookings Date]) = MONTH(MAX(FILTERS(Bookings[Bookings Date]))) ),
                 CALCULATE(SUM(Bookings[Volume]), MONTH(Bookings[Bookings Date]) = MONTH(TODAY())-2 ))

 

I get an error for the formula and I cannot filter a week to get the results of the entire month the week exists in:

 

 

 

Does anyone have an idea on how I can arrange my formula so I can use my week slicer for the results instead of a date slicer?

 

Thanks!

  • Hi Anonymous,

     

    Please create a new table that is a date table. A date table make everything easy in your scenario. The context of the slicer and the table visual and the DAX formula will act on each other. It's hard to make them clear in one table. Please download the demo from the attachment. It will give you some tips. 

    Filtering-a-Month-based-on-a-selection-of-a-Week

     

    Best Regards,
    Dale

9 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion

    The error you are getting is because you are passing a table into MAX. You could instead try MAXX but I am wondering if what you really need is a date table with each week listed and each month listed.

    • Anonymous's avatar
      Anonymous
      Not applicable

      MAXX doesn't work either.  I have a separate date table that has month and week listed for each day.  In fact, I have those fields in my Bookings table which I'm using for my calculation in my original post as well.

      • Anonymous's avatar
        Anonymous
        Not applicable

        I pretty much just need a formula that says

         

        If a week is selected, show sum(Volume) for the month the week exists in, if no selection, show sum(Volume) from previous month.

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Icon for Microsoft Employee rankMicrosoft Employee

    Hi Anonymous,

     

     

    Could you please mark the proper answers as solutions?

     

    Best Regards,

    Dale