how to calculate monday to sunday values

[Anonymous]

Hi 

How to calculate sum of  this week monday to sunday values and following week monday to sunday values.

example

this monday to friday 23/03/2010 - 29/03/2020

following monday 30/03/2020 - 05/04/2020

Thanks in anticipation

Kavitha ghosh

[v-shex-msft · Accepted solution]

Hi @Anonymous,

In fact, you can use year and weeknum function as conditions to compare with date field to lock on a specific week range.
After these steps, you only need to write a common rolling calculation formula with the above conditions to achieve your requirement about weekly rolling total:

Measure =
VAR currDate =
    MAX ( Table[Date] )
RETURN
    CALCULATE (
        SUM ( Table[Amount] ),
        FILTER (
            ALLSELECTED ( Table ),
            WEEKNUM ( [Date], 2 ) = WEEKNUM ( currDate, 2 )
                && YEAR ( [Date] ) = YEAR ( currDate )
                && [Date] <= currDate
        )
    )

Regards,

Xiaoxin Sheng

[amitchandak]

@Anonymous  , You can create a date calendar and week start and week end based on that. You can create a week name based on that. Refer, how to so in the calendar and how to use it

https://www.dropbox.com/s/a9xq913pgvuzg2x/sales_analytics_weekWiseMon_sun.pbix?dl=0