cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

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

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

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

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors