Forum Discussion

KingJoel's avatar
KingJoel
Regular Visitor
2 years ago
Solved

Calculate difference in New measures

Calculate difference in New measure as +/- change and also % change from prior day.  Pulling my hair our crating this.

 

If day is True then subtract prior day that is True show the differance.

also calculate in a  second column as a % of change.

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi KingJoel 

    You can refer to the following sample

    Then line_total and the workday are the measures

    line_total = SUM('Table'[Sum]) 
    Work Day = IF(MAX('Table'[False/true])=1,TRUE(),FALSE())

     Original data 

    You can create the following measures

    Diff =
IF (
    [Work Day] = TRUE (),
    VAR a =
        MAXX (
            FILTER (
                ALLSELECTED ( 'Table' ),
                [load_date] < MAX ( 'Table'[load_date] )
                    && [Work Day] = TRUE ()
            ),
            [load_date]
        )
    RETURN
        [line_total]
            - CALCULATE ( [line_total], 'Table'[load_date] = a )
)

    %diff = [Diff]/[line_total]

    Output

    Best Regards!

    Yolo Zhu

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

2 Replies

  • Kaviraj11's avatar
    Kaviraj11
    Solution Sage
    2 years ago
    Here is an example:
    Changes =
    var _selecteddate= FIRSTDATE('Calendar'[Date])
    var _selecteddate1= FIRSTDATE('Calendar'[Date])-1
    var Total =CALCULATE([YourMeasure),TREATAS({_selecteddate},'Calendar'[Date]))
    var PreviousDayTotal=CALCULATE([YourMeasure),TREATAS({_selecteddate1},'Calendar'[Date]))
    RETURN
    IF([WorkDay]="True",BLANK(),Total -PreviousDayTotal)
     
    Changes % =
    var _selecteddate= FIRSTDATE('Calendar'[Date])
    var _selecteddate1= FIRSTDATE('Calendar'[Date])-1
    var Total =CALCULATE([YourMeasure),TREATAS({_selecteddate},'Calendar'[Date]))
    var PreviousDayTotal=CALCULATE([YourMeasure),TREATAS({_selecteddate1},'Calendar'[Date]))
    RETURN
    IF([WorkDay]="True",BLANK(),((Total -PreviousDayTotal)/Total)*100)
  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi KingJoel 

    You can refer to the following sample

    Then line_total and the workday are the measures

    line_total = SUM('Table'[Sum]) 
    Work Day = IF(MAX('Table'[False/true])=1,TRUE(),FALSE())

     Original data 

    You can create the following measures

    Diff =
IF (
    [Work Day] = TRUE (),
    VAR a =
        MAXX (
            FILTER (
                ALLSELECTED ( 'Table' ),
                [load_date] < MAX ( 'Table'[load_date] )
                    && [Work Day] = TRUE ()
            ),
            [load_date]
        )
    RETURN
        [line_total]
            - CALCULATE ( [line_total], 'Table'[load_date] = a )
)

    %diff = [Diff]/[line_total]

    Output

    Best Regards!

    Yolo Zhu

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