Forum Discussion

Solle's avatar
Solle
Icon for Helper III rankHelper III
4 years ago
Solved

Day to Day difference

Hi All,  I am trying to generate a day to day difference and since I only have data on weekdays and that my data lags 1 day I am facing some issues.  I have the following code: Measure_DTD = ...
Day to Day
  • v-jianboli-msft's avatar
    v-jianboli-msft
    4 years ago

    Hi Solle ,

     

    According to my knowledge, this situation may be caused by the filter "Date Calendar".

    Please try to use the slicer instead of the filter and then test the following measure:

    Measure_Previous Visible Date MV =

VAR ___currentDate = MAX( 'Date Table'[Date] )

VAR ___previousVisibleDate =

                    CALCULATE (

                        MAX ('Date Table'[Date] ),

                        FILTER(ALL('Date Table'),

                        'Date Table'[Date] < ___currentDate))

VAR ___previousVisibleDateMV =

            CALCULATE ( [Measure_MarketValue],

                Filter('Date Table',

 'Date Table' [Date] = ___previousVisibleDate

            ))

RETURN

IF ( NOT ISBLANK( [Measure_MarketValue] ), ___previousVisibleDateMV )


    Measure_MV DtD =

IF(NOT ISBLANK([Measure_Previous Visible Date MV]),

[Measure_MarketValue] - [Measure_Previous Visible Date MV])

     

    If this does not work, please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

    Refer to:

    How to provide sample data in the Power BI Forum

    How to Get Your Question Answered Quickly

     

    Best Regards,

    Jianbo Li

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

daXtreme's avatar
daXtreme
Icon for Solution Sage rankSolution Sage
4 years ago

Solle 

 

Instead of doing all kinds of acrobactics in your code (which you won't understand or remember in a week's time), isn't it better to make the model easier to work with in the first place?

 

"I am trying to generate a day to day difference and since I only have data on weekdays and that my data lags 1 day I am facing some issues."

 

So, if you have data only on weekdays, extend the data so that it's also there on weekends. If your data lags 1 day, then make it so that it does not. Could that not be much easier than that? Please use Power Query to put the data in a shape that will make your journey with DAX a pleasure.

Solle's avatar
Solle
Icon for Helper III rankHelper III
4 years ago

Hi daXtreme 

I just went through my DAX Calculations and tried to simplify it a bit. What I ended up with is first a measure to generate the previous day MV which is as follows:

Measure_Previous Visible Date MV = 
VAR ___currentDate = MAX( 'Date Table'[Date] )
VAR ___previousVisibleDate = 
            IF (NOT ISBLANK( [Measure_MarketValue] ),
                    CALCULATE ( 
                        MAX ('Date Table'[Date] ),
                        'Date Table'[Date] < ___currentDate
                    )
                )
            VAR ___previousVisibleDateMV = 
            CALCULATE ( [Measure_MarketValue],
                'Date Table'[Date] = ___previousVisibleDate
            )
RETURN
    IF ( NOT ISBLANK( [Measure_MarketValue] ), ___previousVisibleDateMV )


Then I created a measure to generate the difference between T and T-1 with the following code:

Measure_MV DtD = 
IF(NOT ISBLANK([Measure_Previous Visible Date MV]), 
    CALCULATE([Measure_MarketValue]) - CALCULATE([Measure_Previous Visible Date MV]))


However this still provides me with blank at the end of the dates in place: 


Do you know how to solve this, with this more simpler code?

Thank you. 
Solle