cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper I

## calculate the six month value starting from the last week.

Hi everyone, I need to calculate the six month value starting from the last week.

i have i column called last week, it return true if the date is last week

here is the dax

LastWeek = IF(DATEDIFF('Date'[FullDate],TODAY(),week)<2 && DATEDIFF('Date'[FullDate],TODAY(),WEEK) = 1 ,TRUE() ,FALSE())

i want to use it as a start date to calculate the last 6 month value.

1 ACCEPTED SOLUTION
Community Support

Do you wan to start the calculate by the last day of last week? I think you can find date six month before last week by EOMONTH function.

``````The six month value starting from the last week =
VAR _STARTDATE =
CALCULATE (
MAX ( 'Date'[FullDate] ),
FILTER ( ALL ( 'Date' ), DATEDIFF ( 'Date'[FullDate], TODAY (), WEEK ) = 1 )
)
VAR _SXIMONTH =
EOMONTH ( _STARTDATE, -6 )
RETURN
CALCULATE (
SUM ( 'Table'[Value] ),
FILTER (
ALL ( 'Date' ),
'Date'[FullDate] > _SXIMONTH
&& 'Date'[FullDate] <= _STARTDATE
)
)``````

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.

3 REPLIES 3
Community Support

Do you wan to start the calculate by the last day of last week? I think you can find date six month before last week by EOMONTH function.

``````The six month value starting from the last week =
VAR _STARTDATE =
CALCULATE (
MAX ( 'Date'[FullDate] ),
FILTER ( ALL ( 'Date' ), DATEDIFF ( 'Date'[FullDate], TODAY (), WEEK ) = 1 )
)
VAR _SXIMONTH =
EOMONTH ( _STARTDATE, -6 )
RETURN
CALCULATE (
SUM ( 'Table'[Value] ),
FILTER (
ALL ( 'Date' ),
'Date'[FullDate] > _SXIMONTH
&& 'Date'[FullDate] <= _STARTDATE
)
)``````

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.

Helper I

This is the right solution.

But the accepted solution button doesn't appear for me under your solution.

Thank you. Very good

Super User

@ALEX2011ALfer_ , try like

Rolling 12 Sales =
var _max =  today() -weekday(today(),2) -6 // or use//  today() -weekday(today(),2)
var _min = date(year(_max), month(_max)-6,1)
return
CALCULATE(SUM(Sales[Sales Amount]),filter(date, date[date] <=_max && date[date] >=_min))

or

Rolling 12 =

var _max =  today() -weekday(today(),2) -6 // or use//  today() -weekday(today(),2)

return

CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],_max ,-6,MONTH))

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors