Filtering a Month based on a selection of a Week

[Anonymous]

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!

[v-jiascu-msft]

Hi @Anonymous,

Could you please mark the proper answers as solutions?

Best Regards,

Dale

[Greg_Deckler]

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]

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]

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]

Hi @Anonymous,

Can you share a dummy sample?

1. If no selection, which month is the previous month?

2. If a week is selected, what about the week spanning two months?

Best Regards,
Dale

[Anonymous]

I've updated the first part of the IF formula to this and it almost gives me the results I'm looking for.  When I select a week from a slicer, the formula below, goes into the Bookings table, and associates which month exists in my selected week, and pulls the Volume total for the MIN(Month) which the selected week exists in (Min(Month) to select a specific month for the instances when a week exists in two months):

Volume MTD Test 2 = IF( ISFILTERED(Bookings[Bookings Week]),
                 CALCULATE(Sum(Bookings[Volume]), FILTER(ALL(Bookings), Bookings[Bookings Month] = MIN(Bookings[Bookings Month]))),
                 CALCULATE(SUM(Bookings[Volume]), MONTH(Bookings[Bookings Date]) = MONTH(TODAY())-2 ))

However, now I'm filtering ALL(Bookings) in the formula above and therefore, when inserting any dimension from the booking table or any other dimension tables, it gives me the total for ALL(Bookings) for a month instead of calculating the totals for each dimension individually.  The results below show up for January when I select week 1 in my slicer.

It seems like such a simple idea, yet I've been struggling for over a week on this issue.  Any help would be appreciated!

Thanks.

[v-jiascu-msft · Accepted solution]

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. 

Best Regards,
Dale

[Anonymous]

Hi @v-jiascu-msft,

Thanks fo rthe suggestion.  I haven't had the chance to test out your solution yet, but I will test it out this week and hopefully it works for me.  Thanks for the tips!

[Anonymous]

Hi @v-jiascu-msft,

1.  If no selection is made, I've currently set the expression to return the results of 2 months ago for the time being, but that can be easily changed by changing the 2 in the ELSE part of the IF statement below:

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

2.  If a week is selected, and it spans multiple months, I just want to use the Max(Month) to give me the results.  If I get this working, I can always change to Min(Month) if the client wishes to use that month instead.

I've pasted screenshots of my sample in my original post, but I can provide another example if needed.

Thank you.