Forum Discussion

clarkey1988's avatar
clarkey1988
Icon for Helper II rankHelper II
6 years ago
Solved

Previous Week Sum Measure

Hi, I'm a fairly new Power BI user and am having a problem calculating a measure for a Matrix visual.

 

My aim is to calculate the previous weeks miles for each business division in a Matrix. My current function calculates the total previous week miles. However, I need it to calculate the previous weeks miles for each business division.

 

Previous Week Miles = CALCULATE(SUM('Trip Detail'[Miles]),(FILTER(ALLSELECTED('Trip Detail'),'Trip Detail'[Week Number]=MAX('Trip Detail'[Week Number])-1)))
 
 
My overall goal is to calculate weekly % change by business division. Please could someone push me in the right direction?
 
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    HI clarkey1988,

    I think you also need to consider about the year conversion issue, so I added if statement your formula to dispatch to two conditions based on current week number that extract from your table date fields. (I add one additional condition to your formula to filter records based on the year and week number, it will be filtered more accurately if your table contains many year records)
    You can try it if below formula meets to your requirement:

    Previous Week =
VAR currDate =
    MAX ( 'Trip Detail'[date] )
RETURN
    CALCULATE (
        SUM ( 'Trip Detail'[Miles] ),
        FILTER (
            ALLSELECTED ( 'Trip Detail' ),
            IF (
                weekNumber > 1,
                YEAR ( 'Trip Detail'[Date] ) = YEAR ( currDate )
                    && WEEKNUM ( 'Trip Detail'[Date] )
                        = WEEKNUM ( currDate ) - 1,
                YEAR ( 'Trip Detail'[Date] )
                    = YEAR ( currDate ) - 1
                    && WEEKNUM ( 'Trip Detail'[Date] )
                        = WEEKNUM ( DATE ( YEAR ( currDate ) - 1, 12, 31 ) )
            )
        ),
        VALUES ( 'Trip Detail'[Business Division] )
    )

    Regards,

    Xiaoxin Sheng

9 Replies

  • PaulDBrown's avatar
    PaulDBrown
    Icon for Community Champion rankCommunity Champion
    6 years ago

    clarkey1988 

    You don't need ALLSELECTED. What is your current week measure? 

    You probably only need something along the lines of:

    Previous week = CALCULATE(SUM('Trip Detail'[Miles]),
FILTER(ALL('Trip Detail'),
'Trip Detail'[Week Number]=MAX('Trip Detail'[Week Number])-1))

     

    If there are other row contexts (nested for example) you might need ALLEXCEPT

    Something along the lines of:

    Previous week = CALCULATE(SUM('Trip Detail'[Miles]),
(FILTER(ALLEXCEPT('Table, Table[Business Division]'),'Trip Detail'[Week Number]=MAX('Trip Detail'[Week Number])-1)))

     

    • clarkey1988's avatar
      clarkey1988
      Icon for Helper II rankHelper II
      6 years ago

      Hi PaulDBrown 

       

      Thanks for responding. My current measure is simply the sum of the current weeks miles/sum of week 10 miles (pre COVID)

       

      Δ Miles = (SUM('Trip Detail'[Miles]))/(CALCULATE(SUM('Trip Detail'[Miles]),'Trip Detail'[Week Number]=10))-1

       

      However, I need it to be week over week and not specific to week 10. I have two filters within the Matrix, but it seems the measure you listed without ALLSELECTED still sums the TOTAL miles, and does not break it out by business division. The business division is a column I added and made into an if statement.

       

       

       

      • PaulDBrown's avatar
        PaulDBrown
        Icon for Community Champion rankCommunity Champion
        6 years ago

        clarkey1988 
        Hi again,

        You probably need to include the fweek number in the ALL clause:

        Previous week = CALCULATE(SUM('Trip Detail'[Miles]),
FILTER(ALL('Trip Detail'[Week Number]),
'Trip Detail'[Week Number]=MAX('Trip Detail'[Week Number])-1))

        Otherwise try with the second option I posted using ALLEXCEPT, see if that works.