topic Re: DAX basis different date columns in DAX Commands and Tips

DAX basis different date columns

sandeep_sharma — Wed, 06 Nov 2024 09:18:30 GMT

Hi,

I have 4 columns...as mentioned in the snapshot. I need to count the total number of Items where Expiration date and received date both are smaller as compared to the 15th of previous month (Current month is selected in a slicer. So if in slicer we select Oct, it should compare the expiration and received dats with 15th of Sep). Also, Billed date should either blank or greater then the 1st day of selected month in slicer.

Re: DAX basis different date columns

johnt75 — Wed, 06 Nov 2024 09:54:48 GMT

Try

Num items = VAR SelectedMonth = MAX ( 'Date'[Date] ) VAR StartCurrentMonth = EOMONTH ( SelectedMonth, -1 ) + 1 VAR MidPrevMonth = EOMONTH ( SelectedMonth, -2 ) + 16 VAR Result = CALCULATE ( COUNTROWS ( 'Table' ), 'Table'[Expiration Date] < MidPrevMonth, 'Table'[Received Date] < MidPrevMonth, 'Table'[Billed Date] > StartCurrentMonth || ISBLANK ( 'Table'[Billed Date] ), REMOVEFILTERS ( 'Date' ) ) RETURN Result

The REMOVEFILTERS is strictly only necessary if you have a relationship from your date table to the fact table.

Also, this assumes that there is one row per item. If that isn't the case then you can replace COUNTROWS with a DISTINCTCOUNT of the id column.

Re: DAX basis different date columns

sandeep_sharma — Wed, 06 Nov 2024 10:34:56 GMT

Thanks johnt75 for the response.

However it is giving an error saying cannot convert value 'Oct' of type text to type date. I have double checked, date in slicer has the date format on it........in the Slicer I am taking only Year and Month in one slicer...please suggest

Re: DAX basis different date columns

johnt75 — Wed, 06 Nov 2024 10:55:05 GMT

Make sure that you are using the right column in the SelectedMonth variable. Where it says MAX( 'Date'[Date]) that needs to be a column of type date. It doesn't matter which column you use in the slicer.

Re: DAX basis different date columns

sandeep_sharma — Wed, 06 Nov 2024 11:39:42 GMT

Thanks johnt75

It is now showing some data atleast. But as I go back in the year, it is reducing the numbers which should not be the case....on investigating I found out that for startcurrentmonth variable it is giving me the data of 1/1/25....any idea why?

Re: DAX basis different date columns

sandeep_sharma — Wed, 06 Nov 2024 11:42:13 GMT

Basically it showing me the date of two months ahead....so if I select nov in slicer then it shows Jan'25 and If I select Jun then it shows Aug'24

Re: DAX basis different date columns

johnt75 — Wed, 06 Nov 2024 11:50:47 GMT

I think in the EOMONTH you've put 2 instead of -2.

Re: DAX basis different date columns

sandeep_sharma — Wed, 06 Nov 2024 12:44:02 GMT

This is awesome! Thank you so much! it worked!!