Forum Discussion
Post ProdigyConvert Comma separated dax measure into a list
Solution SageHi sivarajan21
It appears that your sample data, measure and expected results don't match.
Check that both 'Calendar'[Date] and 'Data'[Date] have a datatype of date (not datetime).
This example would show all dates in 'Calendar'[Date] that are missing a match with 'Data'[Date] --- including all dates in the date table that are before the first 'Data'[Date] and dates after the last 'Data'[Date].
Missing Dates Table 1 =
FILTER(
'Calendar',
NOT ( 'Calendar'[Date] IN VALUES( Data[Date] )
)
)
If you don't want to see those additional dates, maybe try this.
Missing Dates Table 2 =
VAR _StartDt = MINX( ALL( Data[Date] ), Data[Date] ) - 1
VAR _EndDt = MAXX( ALL( Data[Date] ), Data[Date] ) + 1
VAR _Result =
FILTER(
'Calendar',
NOT ( 'Calendar'[Date] IN VALUES( Data[Date] ) )
&& 'Calendar'[Date] >= _StartDt
&& 'Calendar'[Date] <= _EndDt
)
RETURN
_Result
You might need to adjust the _StartDt and _EndDt variables as shown or simply just use the MINX and MAXX values.
In your example without adjusting the variables, the result would be dates from 4/3/2022 to /4/6/2022.
With adjustments made to variab les, the result would be 3/31/2022, 4/3/2022, 4/4/2022, 4/5/2022, 4/6/2022, and 4/12/2022.
Note that with the adjustments made, there could be a problem for the 1st day or last day of your calendar.
I hope this makes sense.
Post ProdigyHi,
Brilliant!
This(Missing Dates table 1) is what i wanted, finding(picking) Calendar dates that is not present(missing) in the Data date column.
I tried this measure in my pbix file and it shows the below error.
I would like to show all the missing dates in the matrix visual(listed one below other) and filtered by points id. The screenshot below shows expected outcome but dates must be listed one below other(list-matrix visual).
Don't want to omit any dates.
Your measure helps me to achieve my below description but I wanted to visualize those missing dates ina matrix visual and it should be filtered by points id.
Description of my original problem:
I have 2 tables calendar table, and Data table. I have provided the sample data of these tables below:
Below is my sample Data table
DATA
| Date | Points | Source | Units | Last_Update | Cost |
| 3/25/2021 12:00:00 AM | NorthYorksCC-2076 | Direct | 84.39 | 2/22/2023 | 0 |
| 3/25/2021 12:00:00 AM | NorthYorksCC-2076 | Direct | 84.39 | 2/22/2023 | 0 |
| 3/27/2021 12:00:00 AM | NorthYorksCC-2076 | Direct | 84.39 | 2/22/2023 | 0 |
| 6/28/2022 12:00:00 AM | INSE-1010 | Invoice | 0.0806 | 2/22/2023 | 0.16 |
| 6/30/2022 12:00:00 AM | INSE-1010 | Invoice | 0.0806 | 2/22/2023 | 0.16 |
Calendar table
| Date |
| 25-Mar-21 |
| 26-Mar-21 |
| 27-Mar-21 |
| 28-Mar-21 |
| 28-Jun-22 |
| 29-Jun-22 |
30-Jun-22 |
we need to find the missing date for each point id from data table by comparing it with calendar table date. for example, from the above sample data, if we look at the data table,
the points (NorthYorksCC-2076) is missing the date 3/26/2021 12:00:00, but when we compare it with Calendar Date, it has 26-Mar-21. similarly, INSE-1010 is missing date 6/29/2022 12:00:00 AM but calendar date, has 29-Jun-22. I need to display these missing dates & their count in output.
My expected outcome could be something like below:
| points id | missing dates measure | no of missing dates measure |
| NorthYorksCC-2076 | 26/03/2021 | 1 |
| INSE-1010 | 29/06/2022 | 1 |
we need one dax measure to display the above missing dates as a list to be shown on the report page.
we need another dax measure to display the count of those above missing dates.
The relationship between above tables are
Please let me know if you need further info
Thanks in advance
- Arul
Super User
this is what I have achieved so far, just able to find the missing dates in a new calculated table,
Max date1 = VAR _distinctDates = VALUES('Table'[Date]) VAR _maxDate = CALCULATE( MAX('Table '[Date]), ALLEXCEPT('Table ','Table '[Points])) VAR _minDate = CALCULATE( MIN('Table '[Date]), ALLEXCEPT('Table ','Table '[Points])) VAR _calendar = CALENDAR( _minDate,_maxDate) VAR _compare = EXCEPT(_calendar,_distinctDates) RETURN _compareThanks,
Arul
- sivarajan21
Hi Arul,
Thanks for your response
However for some reason the table generated has missing year 2022.
Also, I couldn't filter this table using points id even if i establish a relationship between these two.
Thanks in advance.
