Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply

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
v-rzhou-msft
Community Support
Community Support

Hi @ALEX2011ALfer_ ,

 

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.

 

View solution in original post

3 REPLIES 3
v-rzhou-msft
Community Support
Community Support

Hi @ALEX2011ALfer_ ,

 

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.

 

This is the right solution. 

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

Thank you. Very good 

amitchandak
Super User
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))

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors