topic Re: Dynamic Dax based on date filters in DAX Commands and Tips

Dynamic Dax based on date filters

Anonymous — Mon, 21 Mar 2022 23:52:05 GMT

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])))

Re: Dynamic Dax based on date filters

amitchandak — Tue, 22 Mar 2022 03:45:11 GMT

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.

Re: Dynamic Dax based on date filters

tamerj1 — Tue, 22 Mar 2022 04:15:14 GMT

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

Re: Dynamic Dax based on date filters

Anonymous — Tue, 22 Mar 2022 09:37:56 GMT

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?

Re: Dynamic Dax based on date filters

tamerj1 — Tue, 22 Mar 2022 09:44:12 GMT

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

Re: Dynamic Dax based on date filters

Anonymous — Tue, 22 Mar 2022 09:49:14 GMT

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 🙂

Re: Dynamic Dax based on date filters

tamerj1 — Tue, 22 Mar 2022 10:03:09 GMT

Anonymous

Excellent idea 👍

Re: Dynamic Dax based on date filters

tamerj1 — Tue, 22 Mar 2022 10:05:00 GMT

Anonymous

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