cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## Calculate difference between Current period and previous period

Hi All,

Help me to calculate the difference  between current and previous period.

 Previous Period Current Period Current Period - Previous Period Policy Period 18-Dec 19-Jun Diff 15-16 200 300 100 16-17 100 200 100 17-18 200 500 300 18-19 300 400 100 19-20 400 300 -100

Regards,

Suresh Pantra.

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi @Anonymous ,

For your request, I just did some data modeling as follows,

• Unpivoted the Month columns to Rows.
• After that I’ve created the date column based on your month to find the recent month and previous month (DAX 1.1).
• Then find the rank for date column (DAX 1.2).
• After that I’ve created the summarized table to get Diff column header (DAX 1.3) and relate this table into the fact table with inactive relationship.
• Finally create the Final_Output Measure to get this achieved.

DAX 1.1: (Calculated Column)

Date = "01-"&RIGHT('Sample'[Attribute],3)&"-"&LEFT('Sample'[Attribute],2)

DAX 1.2: (Calculated Column)

Rank = RANKX(VALUES('Sample'[Date]),'Sample'[Date],,DESC)

DAX 1.3: (Calculated Table)

Calculated Table = UNION(SUMMARIZE('Sample','Sample'[Attribute])

,SUMMARIZE('Sample',"Attribute","Diff"))

Measure:

Final_Output =

VAR Diff = CALCULATE(SUM('Sample'[Value]),'Sample'[Rank]=2)

VAR Curr = CALCULATE(SUM('Sample'[Value]),'Sample'[Rank]=1)

RETURN

IF(SELECTEDVALUE('Column'[Attribute])="Diff",
CALCULATE(Curr-Diff,ALL('Sample')),
CALCULATE(SUM('Sample'[Value]),
USERELATIONSHIP('Column'[Attribute],'Sample'[Attribute]))
)

Unpivot:

Date & Rank:

Relationship:

Output:

Best Regards,
Mail2inba4

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 REPLIES 3
Anonymous
Not applicable

Hi @Anonymous ,

Could you please share the sample data.

Regards,
Mail2inba4

Anonymous
Not applicable

Hi @Anonymous ,

Here is the sample data.

 Policy Period 18-Jun 18-Dec 19-Jun Diff 15-16 300 200 300 100 16-17 200 100 200 100 17-18 100 200 500 300 18-19 100 300 400 100 19-20 100 400 300 -100

I Need to calculate difference between currentPeriod(19-Jun) and PreviousPeriod(18-dec), ignore 18-jun.

if in future 19 -dec data is inserted it has to take 19-dec as current and 19-jun as previous period.

Dynamically it has to run.

Regards,

Suresh Pantra.

Anonymous
Not applicable

Hi @Anonymous ,

For your request, I just did some data modeling as follows,

• Unpivoted the Month columns to Rows.
• After that I’ve created the date column based on your month to find the recent month and previous month (DAX 1.1).
• Then find the rank for date column (DAX 1.2).
• After that I’ve created the summarized table to get Diff column header (DAX 1.3) and relate this table into the fact table with inactive relationship.
• Finally create the Final_Output Measure to get this achieved.

DAX 1.1: (Calculated Column)

Date = "01-"&RIGHT('Sample'[Attribute],3)&"-"&LEFT('Sample'[Attribute],2)

DAX 1.2: (Calculated Column)

Rank = RANKX(VALUES('Sample'[Date]),'Sample'[Date],,DESC)

DAX 1.3: (Calculated Table)

Calculated Table = UNION(SUMMARIZE('Sample','Sample'[Attribute])

,SUMMARIZE('Sample',"Attribute","Diff"))

Measure:

Final_Output =

VAR Diff = CALCULATE(SUM('Sample'[Value]),'Sample'[Rank]=2)

VAR Curr = CALCULATE(SUM('Sample'[Value]),'Sample'[Rank]=1)

RETURN

IF(SELECTEDVALUE('Column'[Attribute])="Diff",
CALCULATE(Curr-Diff,ALL('Sample')),
CALCULATE(SUM('Sample'[Value]),
USERELATIONSHIP('Column'[Attribute],'Sample'[Attribute]))
)

Unpivot:

Date & Rank:

Relationship:

Output:

Best Regards,
Mail2inba4

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

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

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors