Forum Discussion
DAX basis different date columns
- 1 year ago
I think in the EOMONTH you've put 2 instead of -2.
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.
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
- johnt751 year ago
Super User
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_sharma1 year ago
Helper II
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_sharma1 year ago
Helper II
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