Calculate period over period

[echaize]

Hello,
I'm trying to calculate and compare two measures.

The first "Qty Sum" is the SUM of my fact table on a period defined by a "between" date sclicer and the second is the same measure on the previous period (Qty Sum PP).

For example, if the period is from 11/13/2023 to 11/19/2023 (7 days), I would like to calculate the second measure as the sum on the previous period i.e. 11/06/2023 to 11/12/2023.

If the user selects another period, the measure have to calculate the length of the period and returns the sum of my fact table on this period.

My date table is named "Dates". My fact table is named Facts with a column Date and a column Qty.

The first measure is "Qty Sum" defined as :

Qty Sum =
SUM(Facts[Qty])

The second measure is :

Qty Sum PP =
VAR PeriodEnd = FIRSTDATE('Dates'[Date])-1
VAR PeriodStart =
PeriodEnd -
DATEDIFF(
FIRSTDATE('Dates'[Date]),
LASTDATE('Dates'[Date]),
DAY
)
VAR Result =
CALCULATE(
[Qty Sum],
DATESBETWEEN('Dates'[Date],
PeriodStart,PeriodEnd
)
)
Return Result

The second measure does not work. It returns blank.

Please Help !

[parry2k]

@echaize great it is working, doesn't matter what you do, 2 pcs of advise:

- understand context (row and filter) & context transition

- data modeling

rest is relatively super easy. Cheers!!

[parry2k · Accepted solution]

@echaize you have to remove the row filter context coming because you are showing date column on the row and that's why:

it should be something like this:

Qty Sum PP  = 
VAR __StartDate = CALCULATE ( MIN ( Dates[Date] ), ALLSELECTED ( Dates[Date] ) )
VAR __EndDate = CALCULATE ( MAX ( Dates[Date] ), ALLSELECTED ( Dates[Date] ) ) 
VAR __Days = DATEDIFF ( __EndDate, __StartDate, DAY ) - 1
RETURN 
CALCULATE ( [Qty Sum], DATEADD ( Dates[Date], __Days, DAY ) )

[echaize]

Thanks @parry2k .

It works fine!

I think I should spend more time on understanding context concepts in DAX 😉

[parry2k]

@echaize If I understood it correctly you need this:

Qty Sum PP = 
CALCULATE ( [Qty Sum], DATEADD ( Dates[Date], -7, DAY ) )

[echaize]

I create a new measure according to your suggestion called Qty Sum PP2 :

Qty Sum PP 2 = 
VAR dateoffset = DATEDIFF(FIRSTDATE(Dates[Date]), LASTDATE(Dates[Date]),DAY)*-1
RETURN CALCULATE ( [Qty Sum], DATEADD ( Dates[Date], dateoffset, DAY ) )

It does no work either. The sum is the same as the sum of the current period :

[echaize]

@parry2k Yes, but I want that the offset (-7 in your message) varies according to the offset between the start date and the end date of the slicer.

Thanks.

[parry2k]

That should be easy. Just get max and min date and get the difference days in a variable and then replace -7 with this variable, make sure to make it negative.

[AmiraBedh]

Can you try the

Qty Sum PP =
VAR CurrentPeriodStart = FIRSTDATE('Dates'[Date])
VAR CurrentPeriodEnd = LASTDATE('Dates'[Date])
VAR NumberOfDays = DATEDIFF(CurrentPeriodStart, CurrentPeriodEnd, DAY) + 1
VAR PreviousPeriodStart = CurrentPeriodStart - NumberOfDays
VAR PreviousPeriodEnd = CurrentPeriodEnd - NumberOfDays
RETURN
CALCULATE(
    [Qty Sum],
    DATESBETWEEN(
        'Dates'[Date],
        PreviousPeriodStart,
        PreviousPeriodEnd
    )
)

following? Otherwise can you share your pbix file ?

[echaize]

Hello,

Your solution don't work either.

Here the link of the PBIX file : Test period over period 

Thanks for your help!