Share via

PBI DAX Weekly Total Shown By Day

Charlie Day 1 Reputation point
2021-09-17T19:58:35.21+00:00

I am trying to create a measure that can show weekly aggregate data when sliced/filtered at the day level. More specifically show the same weekly value for each day of the week that is selected. The closest I have gotten is showing that value at the week level but it still slices if you drill to the day level.

Weekly Budget = CALCULATE([Budget],'Fiscal Calendar'[Week Num] = WEEKNUM(TODAY()))

Seems like a minor oversight but everything I try fails at the day.

Thank you for any assistance you can provide.

SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,263 questions
0 comments

4 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Alexei Stoyanovsky 3,416 Reputation points
    2021-09-18T17:56:33.677+00:00

    Besides filtering the week number to that of today, you also need to remove filters (with ALL or REMOVEFILTERS) from any day-grain columns that could be filtered by report users.

    0 comments
  2. Charlie Day 1 Reputation point
    2021-09-18T20:35:15.453+00:00

    Thank you for the response I appreciate the help. Could you provide rough psuedo-code assuming it is a Y-Q-M-W-D calendar table?

  3. Charlie Day 1 Reputation point
    2021-09-19T22:23:04.707+00:00

    Thank you that got me started. I appreciate the help.

    0 comments
  4. Charlie Day 1 Reputation point
    2021-09-27T22:13:35.347+00:00

    This did exactly what I asked it to do, but not what I needed it to do. I am pulling this calculation into Power BI and it works fine on a Card visual. Any other visual does not seem to recognize the ALL() statement as it filters down for whatever time period is selected. Is there something special that needs to be done for Power BI visuals to understand how to work with the ALL() filters?