Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
I am currently having issues with a specific use-case where I need to get both date values from a date slicer and then use it in a formula:
This slicer uses data from a single column called Document Date
This is what my slicer looks like.
What I want is to check if a document was received in a period that refers to the end date of that slicer, minus 12 months, and put the number of the document in a column. If the document was not received in the said period, the column will have a value of 0.
I checked lots of solutions, but all of them use 2 columns for the date slicer, but for my use case I want to use just one.
Thank you in advance for your support.
Solved! Go to Solution.
@T_von_Axt , Try a measure like
measure =
var _max = maxx(allselected('Table'),'Table'[Date]) // Use Date(2021,01,31)
var _min = eomonth(_max,-12) +1
var _calc = calculate(count(Table[Document No]), filter(all('Table'), 'Table'[Date] >=_min && 'Table'[Date] <=_max))
return
if(isblank(_calc) && max('Table'[Date]) <_max , 0, _calc)
But better have date table. Independent date table will do better
measure =
var _max = maxx(allselected('Date'),'Date'[Date]) // Use Date(2021,01,31)
var _min = eomonth(_max,-12) +1
var _calc = calculate(count(Table[Document No]), filter(('Table'), 'Table'[Date] >=_min && 'Table'[Date] <=_max))
return
if(isblank(_calc) && max('Table'[Date]) <_max , 0, _calc)
@T_von_Axt , Try a measure like
measure =
var _max = maxx(allselected('Table'),'Table'[Date]) // Use Date(2021,01,31)
var _min = eomonth(_max,-12) +1
var _calc = calculate(count(Table[Document No]), filter(all('Table'), 'Table'[Date] >=_min && 'Table'[Date] <=_max))
return
if(isblank(_calc) && max('Table'[Date]) <_max , 0, _calc)
But better have date table. Independent date table will do better
measure =
var _max = maxx(allselected('Date'),'Date'[Date]) // Use Date(2021,01,31)
var _min = eomonth(_max,-12) +1
var _calc = calculate(count(Table[Document No]), filter(('Table'), 'Table'[Date] >=_min && 'Table'[Date] <=_max))
return
if(isblank(_calc) && max('Table'[Date]) <_max , 0, _calc)
Hi again @amitchandak,
It is not quite what I want to achieve yet.
Do you know how can I get the date used as the end date in that slicer?
Thank you in advance.
Hi @amitchandak,
Thank you for your answer.
I am currently trying to implement this, but Power BI keeps throwing an error: "The syntax for 'var' is incorrect. (DAX(var _max = maxx(allselected(GENERAL_DATA[DATE]),GENERAL_DATA[DATE] // Use Date(2021,01,31)var _min = eomonth(_max,-12) +1var _calc = calculate(count(GENERAL_DATA[DOCUMENT_NUMBER]), filter((GENERAL_DATA), GENERAL_DATA[DATE] >=_min && GENERAL_DATA[DATE] <=_max))returnif(isblank(_calc) && max(GENERAL_DATA[DATE]) <_max , 0, _calc)))."
Do you know what am I doing wrong here?
Edit: It was just a missing parenthesis.
@T_von_Axt , This would be like
var _max = maxx(allselected(GENERAL_DATA[DATE]),GENERAL_DATA[DATE]) //
var _min = eomonth(_max,-12) +1
var _calc = calculate(count(GENERAL_DATA[DOCUMENT_NUMBER]), filter(all(GENERAL_DATA), GENERAL_DATA[DATE] >=_min && GENERAL_DATA[DATE] <=_max))
return
if(isblank(_calc) && max(GENERAL_DATA[DATE]) <_max , 0, _calc)
But remember when you use date in the slicer and try using that for a bigger date range than selected then you need all to remove filter
Hello again @amitchandak,
It worked now, but when I insert the measure into a table, it keeps loading but shows nothing.
Do you think this is related to the data volume (12 million rows)?
Thank you in advance.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |