Forum Discussion
DAX basis different date columns
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.
I think in the EOMONTH you've put 2 instead of -2.
7 Replies
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 ResultThe 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.
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
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.
