Dynamic Dax based on date filters

[Anonymous]

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 · Accepted solution]

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]

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]

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

[Anonymous]

Yes, but unfortunately when changing the date slicer , the columns appear again, I thought maybe creating two visuals and grouping them, if there is a better solution I would appreciate it Thanks 🙂

[tamerj1]

@Anonymous 

If you find my reply helpful kindly consider marking it as accepted solution. 

[tamerj1]

@Anonymous 

Excellent idea 👍

[amitchandak]

@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.