topic Re: Calculate period over period in DAX Commands and Tips

Calculate period over period

echaize — Sat, 18 Nov 2023 10:11:18 GMT

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.

Any help would be appreciated!

Re: Calculate period over period

lbendlin — Sun, 19 Nov 2023 23:50:30 GMT

I'm trying to calculate and compare two measures.

You cannot measure a measure. Implement your business logic in a single measure.

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
If you are unsure how to do that please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.

If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: Calculate period over period

echaize — Mon, 20 Nov 2023 07:10:50 GMT

Hello,

parry2k provides the solution in this post : https://community.fabric.microsoft.com/t5/Desktop/Calculate-period-over-period/td-p/3540117

Regards