DAX basis different date columns

[sandeep_sharma]

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.

[johnt75]

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.

[sandeep_sharma]

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

[johnt75]

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.

[sandeep_sharma]

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?

[sandeep_sharma]

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

[johnt75 · Accepted solution]

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

[sandeep_sharma]

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