topic Re: DAX calculate if datedif between slicer selected date and column date equal to value in DAX Commands and Tips

DAX calculate if datedif between slicer selected date and column date equal to value

Anonymous — Thu, 03 Jun 2021 06:58:14 GMT

I'm making calculated buckets based on the date difference. Currently, the bucket is generated based on the last date of the current month as below measure.

Measure =CALCULATE(SUM(RF[Cost]),DATEDIFF(EOMONTH(TODAY(),0),RF[PoDate],DAY)<30)

Basically this gives sum of the cost if the count of the dates between the current month end date and podate lesser than 30. But i need EOMONTH(TODAY(),0) to be replaced with a user-selected value using a slicer or any other method.

Additionally, I have created

Parameter = GENERATESERIES(DATE(2021,01,01),DATE(2023,01,01), 1)

Parameter Value = SELECTEDVALUE('Parameter'[Parameter])

where user to select the date using slicer. But when i replace Parameter value with EOMONTH(TODAY(),0) like below

Measure = CALCULATE(SUM(BIItemLedgerRF[CostAmountActual]),DATEDIFF(MIN(Parameter[Parameter Value]),BIItemLedgerRF[PoDate.PoDate],DAY)<30)

But It generates an error by telling Column 'Parameter Value' in table 'Parameter' cannot be found or may not be used in this expression.

Please help me to sort this out struggling with this for a long time.

Re: DAX calculate if datedif between slicer selected date and column date equal to value

amitchandak — Thu, 03 Jun 2021 11:01:15 GMT

Anonymous , make sure the names are correct.

Also try like , You need have some grouping level for this. I assumed - BIItemLedgerRF[Bill ID]

Measure =
var _min =MINX(allselected(Parameter),Parameter [Parameter Value])
return
CALCULATE(SUM(BIItemLedgerRF[CostAmountActual]),filter(values(BIItemLedgerRF[Bill ID]) , DATEDIFF(_min,max(BIItemLedgerRF[PoDate.PoDate]),DAY)<30))

You need to get dynamic bucketing. Dynamic Segmentation, Bucketing or Binning: https://youtu.be/CuczXPj0N-k

also refer date diff across table

https://community.powerbi.com/t5/Community-Blog/Decoding-Direct-Query-in-Power-BI-Part-2-Date-Difference-Across/ba-p/934397#M451

Re: DAX calculate if datedif between slicer selected date and column date equal to value

Anonymous — Thu, 03 Jun 2021 11:03:51 GMT

Anonymous

It can't be used in this expression because logical expressions under CALCULATE can't contain measures. They can only be of this form (more or less):

F(T[Col]) <operator> Value,

where Value is a constant and F is a function that depends only on one column in a base table, never measures.

Therefore, first capture the value of the measure in a variable and then use the variable in a well-formed expression. And you don't need any MIN around the parameter value. You should also follow Best Practices when formatting and writing DAX code. Otherwise, you'll get into trouble rather sooner than later.

Rules for DAX Code Formatting - SQLBI

Re: DAX calculate if datedif between slicer selected date and column date equal to value

Anonymous — Thu, 03 Jun 2021 12:08:31 GMT

The method you mentioned has worked. Additionally by following the references you mentioned, found this article https://www.credera.com/insights/creating-aging-report-using-a-user-selected-date-in-power-bi on how to handle bucketing.

Thank you amitchandak

Re: DAX calculate if datedif between slicer selected date and column date equal to value

Anonymous — Thu, 03 Jun 2021 12:11:10 GMT

Anonymous Found the solution from another user. Till today I have not followed formating since I was transferred to DAX from M query very recently. Thank you for aware me of best practising on DAX code and for the reference.