Skip to main content
cancel
Showing results for 
Search instead 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

Reply
Anonymous
Not applicable

Calculate difference between Current period and previous period

Hi All,

 

Could you please help writing DAX code for below query:

 

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

 Previous PeriodCurrent PeriodCurrent Period - Previous Period
Policy Period18-Dec19-JunDiff
15-16200300100
16-17100200100
17-18200500300
18-19300400100
19-20400300-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:

Unpivot.PNG

 

Date & Rank:

Data.PNG

Relationship:

Relation.PNG

Output:

Capture.PNG

Best Regards,
Mail2inba4

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
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 Period18-Jun18-Dec19-JunDiff
15-16300200300100
16-17200100200100
17-18100200500300
18-19100300400100
19-20100400300-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:

Unpivot.PNG

 

Date & Rank:

Data.PNG

Relationship:

Relation.PNG

Output:

Capture.PNG

Best Regards,
Mail2inba4

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

Helpful resources

Announcements
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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