Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Dynamic Dax based on date filters

Hi, 
I'm trying to create a dynamic table based on date selection as the below DAX, but the problem is in the output where the Difference appears on both dates , not as a total difference, does anyone have a solution for this?
 

DAX 

Difference =
var _max = maxx(allselected('last month GL summary'),'last month GL summary'[MIS_DATE])
var _min = minx(allselected('last month GL summary'),'last month GL summary'[MIS_DATE])
return
(calculate(SUMX(filter('last month GL summary','last month GL summary'[MIS_DATE]=_max),'last month GL summary'[USD_currency]))-calculate(SUMX( filter('last month GL summary','last month GL summary'[MIS_DATE] =_min),'last month GL summary'[USD_currency])))
 
 
  • tamerj1's avatar
    tamerj1
    4 years ago

    Salam Anonymous 

    This will return the desired result under the 2nd date

    Difference =
VAR PreviousDate =
    MAX ( 'last month GL summary'[MIS_DATE] ) - 1
VAR CurrentDateValue =
    SUM ( 'last month GL summary'[USD_currency] )
VAR PreviousDateValue =
    CALCULATE (
        SUM ( 'last month GL summary'[USD_currency] ),
        'last month GL summary'[MIS_DATE] = PreviousDate
    )
RETURN
    CurrentDateValue - PreviousDateValue

     

     

     

7 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    Anonymous , if you try Day on day diff you should use date table

     

    example

     

    This Day = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])))
    Last Day = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])-1))
    Last Day = CALCULATE(sum('Table'[Qty]), previousday('Date'[Date]))

     

    diff =[This Day] - [Last Day]

     

     

    Day Intelligence - Last day, last non continous day
    https://medium.com/@amitchandak.1978/power-bi-day-intelligence-questions-time-intelligence-5-5-5c3243d1f9

     

    Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
    https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5bd4
    To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Salam Anonymous 

    This will return the desired result under the 2nd date

    Difference =
VAR PreviousDate =
    MAX ( 'last month GL summary'[MIS_DATE] ) - 1
VAR CurrentDateValue =
    SUM ( 'last month GL summary'[USD_currency] )
VAR PreviousDateValue =
    CALCULATE (
        SUM ( 'last month GL summary'[USD_currency] ),
        'last month GL summary'[MIS_DATE] = PreviousDate
    )
RETURN
    CurrentDateValue - PreviousDateValue

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thanks a lot, I fixed the equation but I still have another problem , I don't want to show the columns with Differences zero I only want to show the final difference in the matrix, Is there a solution for that?

       

      • tamerj1's avatar
        tamerj1
        Icon for Community Champion rankCommunity Champion
        4 years ago

        Anonymous 
        Easiest way is to hide it simply using your mouse - reduce the column width to zero