Dax total tickets last 4 fiscal weeks not including current week

[romoguy15]

Hello, 

I am trying to figure out a dax measure that gives me my total count of tickets based on the last 4 fiscal weeks not including the current fiscal week we are in. Below is my measure I am using my it is not correctly calculating. I have some sample data to share if anyone can assist.

https://1drv.ms/u/s!AqID1H0nHPOzhAsuNYCFESehpBTd?e=yiIFt1

Total Tickets(Past 4 Weeks) =

CALCULATE([Total Tickets],

FILTER('Calendar', 'Calendar'[FiscalWeek]<CALCULATE(MAX('Calendar'[FiscalWeek]),

FILTER('Calendar',[Date]=TODAY())) && 'Calendar'[FiscalWeek]>=CALCULATE(MAX('Calendar'[FiscalWeek]),

FILTER('Calendar',[Date]=TODAY()))-4))

[Greg_Deckler · Accepted solution]

@romoguy15 I think a lot of this had to do with you using Page Filters instead of visual filters. For example, of course your "Total Tickets(Past 4 Weeks)" showed blank because you filtered out TODAY essentially. In any event, here is an improved formula that will ignore filters. You are going to have an issue though as you cycle years. I highly recommend a sequential fiscal week number to avoid this sort of thing or perhaps you have a different method of rolling over years. Here is the new formula and updated PBIX with filters corrected below sig. I only ever got the 121 figure when I used ALL in the formula and did not filter by year = YEAR(TODAY()) so I'm guessing that you had 4 tickets in 2020 in the same fiscal year.

Total Tickets(Past 4 Weeks) 2 = 
CALCULATE([Total Tickets],
    FILTER(ALL('Calendar'), 'Calendar'[Year] = YEAR(TODAY()) && 'Calendar'[FiscalWeek]<CALCULATE(MAX('Calendar'[FiscalWeek]),
            FILTER('Calendar',[Date]=TODAY())) && 'Calendar'[FiscalWeek]>=CALCULATE(MAX('Calendar'[FiscalWeek]),
                    FILTER('Calendar',[Date]=TODAY()))-4))

[romoguy15]

@Greg_Deckler  Dude that post for the seqential week column is awesome! This really helps me on so many other formulas that I could not solve for.

[romoguy15]

Hey @Greg_Deckler , I understand your mention of the page level filters, but even if I removed that and manually selected the fiscal weeks in the table, it still comes up blank. Unfortunately 121 is still not the right total.  Regarding the sequential weeks, are you referring to creating some sort of index for the calendar? I think I understand what you are asking. Essentially the fiscal weeks causes issues because any week could be in multiple years. So I did some digging on those 4 that total up to 121 and essentially those 4 fall within the 4 fiscal weeks of 20-23 but that is not good since it's last year. I really want to avoid this.

[Greg_Deckler · Accepted solution]

@romoguy15 I think you need to download and look at the PBIX file I attached as I fixed everything so that it returns 117, not 121. Perhaps I was unclear about that. There are numerous ways to get a sequential week number, here is a DAX way I came up with:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Sequential/m-p/380231#M116

[romoguy15]

@Greg_Deckler  Sorry about that. I completely read right over the download link. I do see the fix which is fantastic. I guess I did misunderstand if the new formula would avoid the issue I had prior? Is the sequential week number still need in this particular scenario? I know for sure I'll have many more instances of needing to go back to prior fiscal weeks and want to avoid suming up prior years.

[Greg_Deckler]

@romoguy15 Should avoid the issues you had even with page filters because of the ALL.

That said, no, the fix does not include a sequential fiscal week number which makes things easier. There are ways to do it without it but it is a lot of checking what week you are in. It's not the end of the world but way more painful than setting up a sequential number most times.

[romoguy15]

@Greg_Deckler Thanks for all the help. I added the seqential week column into my calendar and tested it in my old formula and it solved the issue. Thanks again. I will probably use your improved one to avoid page level filters so that's also great.

[Greg_Deckler · Accepted solution]

@romoguy15 I think a lot of this had to do with you using Page Filters instead of visual filters. For example, of course your "Total Tickets(Past 4 Weeks)" showed blank because you filtered out TODAY essentially. In any event, here is an improved formula that will ignore filters. You are going to have an issue though as you cycle years. I highly recommend a sequential fiscal week number to avoid this sort of thing or perhaps you have a different method of rolling over years. Here is the new formula and updated PBIX with filters corrected below sig. I only ever got the 121 figure when I used ALL in the formula and did not filter by year = YEAR(TODAY()) so I'm guessing that you had 4 tickets in 2020 in the same fiscal year.

Total Tickets(Past 4 Weeks) 2 = 
CALCULATE([Total Tickets],
    FILTER(ALL('Calendar'), 'Calendar'[Year] = YEAR(TODAY()) && 'Calendar'[FiscalWeek]<CALCULATE(MAX('Calendar'[FiscalWeek]),
            FILTER('Calendar',[Date]=TODAY())) && 'Calendar'[FiscalWeek]>=CALCULATE(MAX('Calendar'[FiscalWeek]),
                    FILTER('Calendar',[Date]=TODAY()))-4))