cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Regular Visitor

## The difference between the earliest two dates

Hello,

I have the table shown, I need a measure (1) that calculates the difference between the earliest two dates

and measure (2) how many times the price changed.

2 ACCEPTED SOLUTIONS
Super User

Simple enough,

``````Cnt =
CALCULATE(
COUNTROWS( 'PRICE' ),
'PRICE'[#]
<= MAXX(
INDEX( 2, DISTINCT( 'PRICE' ), ORDERBY( 'PRICE'[Date], DESC ) ),
'PRICE'[#]
)
) - 1``````

 Expertise = List.Accumulate(        {Days as from Today},        {Skills and Knowledge},        (Current, Everyday) => Current & Day.LeanAndPractise(Everyday))

Super User

Hi,

PBI file attached.

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
3 REPLIES 3
Super User

Hi,

PBI file attached.

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User

Simple enough,

``````Cnt =
CALCULATE(
COUNTROWS( 'PRICE' ),
'PRICE'[#]
<= MAXX(
INDEX( 2, DISTINCT( 'PRICE' ), ORDERBY( 'PRICE'[Date], DESC ) ),
'PRICE'[#]
)
) - 1``````

 Expertise = List.Accumulate(        {Days as from Today},        {Skills and Knowledge},        (Current, Everyday) => Current & Day.LeanAndPractise(Everyday))

Super User

Hi,

I am not sure how your semantic model looksl like but please check the below picture and the attached pbix file.

INDEX function (DAX) - DAX | Microsoft Learn

``````latest date value vs. second latest date value: =
VAR _t =
FILTER (
ALL ( 'Calendar'[Date] ),
CALCULATE ( SUM ( Data[Price] ) ) <> BLANK ()
)
VAR _latest =
CALCULATE (
SUM ( Data[Price] ),
INDEX ( 1, _t, ORDERBY ( 'Calendar'[Date], DESC ) )
)
VAR _second =
CALCULATE (
SUM ( Data[Price] ),
INDEX ( 2, _t, ORDERBY ( 'Calendar'[Date], DESC ) )
)
RETURN
IF ( HASONEVALUE ( 'ID'[ID] ), _latest - _second )
``````

OFFSET function (DAX) - DAX | Microsoft Learn

``````price change count: =
VAR _nonblankdate =
FILTER (
VALUES ( 'Calendar'[Date] ),
CALCULATE ( SUM ( Data[Price] ) ) <> BLANK ()
)
VAR _t =
FILTER (
SUMMARIZE ( Data, 'Calendar'[Date] ),
"@current", CALCULATE ( SUM ( Data[Price] ) ),
"@prev",
CALCULATE (
SUM ( Data[Price] ),
OFFSET ( -1, _nonblankdate, ORDERBY ( 'Calendar'[Date], ASC ) )
)
),
[@prev] <> BLANK ()
)
RETURN
IF (
HASONEVALUE ( 'ID'[ID] ),
COUNTROWS ( FILTER ( _t, [@current] <> [@prev] ) )
)
``````

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors