Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
Merle
Frequent Visitor

Finding the difference between date values in the same row of different column within a Matrix

Hi,

I will greatly appreciate help in finding the difference between dates values within a matrix inorder to determine WoW%.

Desired out put in screen shot below.

This visual was obtain using the new tool New Calculation(fx) but has limitation of not being able to export data with these calculations.

Desire output.PNG

These are the calculation used.

Versus previous = [Purchase Complete] - PREVIOUS([Purchase Complete], COLUMNS).
WOW = FORMAT(DIVIDE([Versus previous], PREVIOUS([Purchase Complete],COLUMNS),0),"#,#0%").

 

2 ACCEPTED SOLUTIONS
Anonymous
Not applicable

@Joe_Barry Thanks for your contribution on this thread.

Hi @Merle ,

You can create a measure as below to get it, please find the details in the attachment.

WOW = 
VAR _pcdate =
    SELECTEDVALUE ( 'Table'[Purchase Complete] )
VAR _category =
    SELECTEDVALUE ( 'Table'[Category] )
VAR _predate =
    CALCULATE (
        MAX ( 'Table'[Purchase Complete] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Category] = _category
                && 'Table'[Purchase Complete] < _pcdate
        )
    )
VAR _prevalue =
    CALCULATE (
        SUM ( 'Table'[Sales] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Category] = _category
                && 'Table'[Purchase Complete] = _predate
        )
    )
RETURN
    DIVIDE ( SUM ( 'Table'[Sales] ) - _prevalue, _prevalue )  

vyiruanmsft_0-1713418698168.png

Best Regards

View solution in original post

 
Thank you very much v-yiruan-msft

Solution works.

View solution in original post

7 REPLIES 7
Merle
Frequent Visitor

Hi Joe,

Thanks for your response. Dax Formula used is giving the same value as the date column not the difference between the column dates of the previous. Is there a different way to format this formula? Thank you.

Capture.PNG

Anonymous
Not applicable

@Joe_Barry Thanks for your contribution on this thread.

Hi @Merle ,

You can create a measure as below to get it, please find the details in the attachment.

WOW = 
VAR _pcdate =
    SELECTEDVALUE ( 'Table'[Purchase Complete] )
VAR _category =
    SELECTEDVALUE ( 'Table'[Category] )
VAR _predate =
    CALCULATE (
        MAX ( 'Table'[Purchase Complete] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Category] = _category
                && 'Table'[Purchase Complete] < _pcdate
        )
    )
VAR _prevalue =
    CALCULATE (
        SUM ( 'Table'[Sales] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Category] = _category
                && 'Table'[Purchase Complete] = _predate
        )
    )
RETURN
    DIVIDE ( SUM ( 'Table'[Sales] ) - _prevalue, _prevalue )  

vyiruanmsft_0-1713418698168.png

Best Regards

Hi @Anonymous,

Thank you again for your solution to my previous question. 

I have encountered an issue in finding the Conversion rate (CR) of two measures between dates. Can you please assist me with this?

CR Formula = Upgrade / Signups

Expected results:

Capture 1.PNG

 

I have tried your dax formula with a slight modification:

Capture.PNG

 

This is my result, it is only calculating the first date between Upgrade and Signups.

 

InkedCapture_LI.jpg

 

 

Merle
Frequent Visitor

Found a Solution to this

Merle
Frequent Visitor

Found a solution to this.

 
Thank you very much v-yiruan-msft

Solution works.

Joe_Barry
Super User
Super User

Hi @Merle 

 

Maybe this will help?  https://community.fabric.microsoft.com/t5/Desktop/WoW-trend-calculation/m-p/533645/highlight/true#M2...

 

Thanks

Joe

 

If you found my answer helpful and it solved your issue, please accept as solution




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





Learn about the Star Schema, it will solve many issues in Power BI!

Date tables help! Learn more



LinkedIn
Let's connect on LinkedIn


Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

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

June 2025 community update carousel

Fabric Community Update - June 2025

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