Hi all,
I would like your advice if it is possible to write a statement on cumulative total that sums up only to the Sunday of last week.
I have added the Date Query table and would want the statement to sum as follows:
MTD Sales = Calculate Sum of Quantity Sold from 01-June up to 23 June. (WK25, Sunday).
This statement would have to exclude any Quantity sold on the current week 26.
Regards
Hidayat
Try not to be scared by the size, but I tried to as many variables were needed to understand the concept. Always when we have to work with weeks in Power BI is harder… but, once understand some concepts in DAX, then it gets easier.
Measure =
VAR LastDay =
TODAY () // Can change for Max(fact[Date]) or Max(Calendar[Date]) . depends of your analysis
VAR CurrentYear =
YEAR ( LastDay )
VAR MaxWeek =
VALUE ( LOOKUPVALUE ( Calendar[WeekNum]; Calendar[Date]; LastDay ) ) // Week of the year. example: 26
VAR YearPreviousWeek =
IF (
MaxWeek = 1;
// Checking if the week is the first of the Year
CurrentYear - 1;
// Changeing to get Last Week of last Year. but if you have no sum() for the first week fo the year. replace the -1 with 0
CurrentYear
)
VAR PreviousWeek =
IF (
MaxWeek = 1;
// Checking if the week is the first of the Year
CALCULATE (
MAX ( Calendar[WeekNum] );
VALUE ( Calendar[Year] ) = ( CurrentYear - 1 )
);
// Samething. if you have no sum(). replace with an non exist week or blank()
MaxWeek - 1
)
VAR LastDatePreviousWeek =
CALCULATE (
MAX ( Calendar[Date] );
FILTER (
ALL ( Calendar[Date]; Calendar[Year]; Calendar[WeekNum] );
VALUE ( Calendar[Year] ) = YearPreviousWeek
&& VALUE ( Calendar[WeekNum] ) <= PreviousWeek
)
)
VAR ResultAsCard =
// To show as a Card (Fixed Amount of the calculation
CALCULATE (
SUM ( FactSales[Amount] );
FILTER (
ALL ( Calendar );
VALUE ( Calendar[Year] ) = YearPreviousWeek
&& Calendar[Date] <= LastDatePreviousWeek
)
)
RETURN
IF (
MAX ( Calendar[Date] ) <= LastDatePreviousWeek;
// To show Acumulating (If you segmenting by date.week.month.etc… )
CALCULATE (
SUM ( FactSales[Amount] );
FILTER (
ALL ( Calendar );
VALUE ( Calendar[Year] ) = YearPreviousWeek
&& Calendar[Date] <= MAX ( Calendar[Date] )
)
);
//Checking if is a selection in Month/Week (and you can add more much… ) to only show this calculation when is in total/card
IF (
OR ( HASONEVALUE ( Calendar[Month] ); HASONEVALUE ( Calendar[WeekNum] ) );
BLANK ();
ResultAsCard
)
)
Is it possible to load in a demo file that you are working on. I’m also happy to have a look and suggest a formula.
Thanks
Sam
