Forum Discussion

SeanB004's avatar
SeanB004
Frequent Visitor
2 years ago
Solved

Duplicate values for Previous Week measure

Hi All,  I have a measure calculating Current Week and Previous week sales, grouped by the Status. However when it shows the previous week value, it duplicates the value and doesnt show it's statuse...
Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi SeanB004 ,

     

    Please try code as below.

     

    PreviousWeekOppts =
VAR _SUMMARIZE =
    SUMMARIZE (
        'Oppts (2)',
        'Oppts (2)'[Week Ending Date],
        'Oppts (2)'[Status],
        "PreviousWeekOppts",
            CALCULATE (
                [Number of Oppts],
                FILTER (
                    ALL ( 'Oppts (2)' ),
                    'Oppts (2)'[Status] = MAX ( 'Oppts (2)'[Status] )
                        && 'Oppts (2)'[Week Ending Date]
                            = MAX ( 'Oppts (2)'[Week Ending Date] ) - 7
                )
            )
    )
RETURN
    SUMX ( _SUMMARIZE, [PreviousWeekOppts] )

     

    Result is as below.

     

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

amitchandak's avatar
amitchandak
Super User
2 years ago

SeanB004 , Try with a date table

 

Have these new columns in Date Table, Week Rank is Important in Date/Week Table

Week Rank = RANKX('Date','Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX('Date','Date'[Year Week],,ASC,Dense) //YYYYWW format


These measures can help
This Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))

 

 

Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
https://www.youtube.com/watch?v=pnAesWxYgJ8
Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510