Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anett_R
Frequent Visitor

Compare date from fact table with date in matrix with dax measure

Hi everyone,

 

I am having trouble with the following issue:

  • I have a date table called 'DimDate' and a table with inventory information 'YART1025 - Bestand' for individual items and an inventory date.
  • Example: The item '220261' has an inventory value of 1,526 pieces on 2023/05/02 (second may 2023).
  • What I did: I created a matrix visual as following: 
 DimDate[Date]
ArticleMeasure [Test]

 

  • I then created a measure called [Test] with an IF statement that if the inventory date matchs with the date in the matrix column, the value 1 is output:

 

 

 

 

 

Test = 
var _InventoryDate = MAX('YART1025 - Bestand'[JAHRMONAT]) 
var _MatrixDate = SELECTEDVALUE(DimDate[JAHRMONAT])

RETURN
IF(
    _inventoryDate = _MatrixDate, 
    1,
    blank()
)​

 

 

 

 

 

This has worked so far:

Anett_R_3-1683280239471.png

However, if I change this measure to the following condition, no correct values come out. What could be the reason for this?

 

 

 

 

 

Test = 
var _InventoryDate = MAX('YART1025 - Bestand'[JAHRMONAT]) 
var _MatrixDate = SELECTEDVALUE(DimDate[JAHRMONAT])

RETURN
IF(
    _inventoryDate < _MatrixDate, 
    1,
    blank()
)

 

 

 

 

 

Anett_R_4-1683280520702.png

The matrix shows results up to year 2000, even though the inventory date is in 2023 and is bigger than the date in the column of the matrix, so I'm expecting a blank result here. Same happens, if I change the measure in _inventoryDate > _MatrixDate. Every column gets a '1' as a result except the column 20235, where _inventoryDate = _MatrixDate.

 

 

Data model:

 

Anett_R_2-1683279971518.png

 

Thank you for your help in advance!

 

Anett

 

 

*****EDIT

I tried to clear some data types (its all decimals now) and re-wrote the measure into following (don't worry about the new column reference "YYYYMM" in the table DimDate): 

Test = 
var _InventoryDate = MAX('YART1025 - Bestand'[JAHRMONAT]) 
var _MatrixDate = SELECTEDVALUE(DimDate[YYYYMM])

RETURN
SWITCH(
    TRUE(),
    _InventoryDate = _MatrixDate, 1,
    _InventoryDate < _MatrixDate, 0,
    _InventoryDate > _MatrixDate, 2,
    blank()
)

The result looks like this: 

Anett_R_0-1683288948705.png

So this measure shows, that the date in Matrix is always bigger than the inventory date - except in the month of the inventory. So at least, this part is correct. I really don't understand this result.

1 ACCEPTED SOLUTION
Anett_R
Frequent Visitor

For anyone who stumbles across the same issue - that gave me the solution: 

Because there was no inventory date in the later/sooner month columns of the matrix, the DAX formula had to compare a blank value (inventory date) with the matrix date. That has led to the result that the comparison in the DAX formula has determined that the inventory date is less than the matrix date.

What I did: I rewrote the DAX for identifying the inventory date like this: 

 

_InventoryDate = 
CALCULATE(
        MAX('YART1025 - Bestand'[JAHRMONAT]),
        REMOVEFILTERS(DimDate2)
    )

 

As a result, the inventory date is being displayed for every column of the matrix regardless of the matrix date. Like that, the comparison of both dates can be performed:

 

Test2 = 
var _InventoryDate = 
CALCULATE(
        MAX('YART1025 - Bestand'[JAHRMONAT]),
        REMOVEFILTERS(DimDate2)
    )
var _MatrixDate = SELECTEDVALUE(DimDate2[YYYYMM])

RETURN
SWITCH(
    TRUE(),
    _MatrixDate = _InventoryDate, 1,
    _MatrixDate > _InventoryDate, 0,
    _MatrixDate < _InventoryDate, 2,
    blank()
)

 

Result: 

Anett_R_0-1683529252510.png

 

View solution in original post

4 REPLIES 4
Anett_R
Frequent Visitor

For anyone who stumbles across the same issue - that gave me the solution: 

Because there was no inventory date in the later/sooner month columns of the matrix, the DAX formula had to compare a blank value (inventory date) with the matrix date. That has led to the result that the comparison in the DAX formula has determined that the inventory date is less than the matrix date.

What I did: I rewrote the DAX for identifying the inventory date like this: 

 

_InventoryDate = 
CALCULATE(
        MAX('YART1025 - Bestand'[JAHRMONAT]),
        REMOVEFILTERS(DimDate2)
    )

 

As a result, the inventory date is being displayed for every column of the matrix regardless of the matrix date. Like that, the comparison of both dates can be performed:

 

Test2 = 
var _InventoryDate = 
CALCULATE(
        MAX('YART1025 - Bestand'[JAHRMONAT]),
        REMOVEFILTERS(DimDate2)
    )
var _MatrixDate = SELECTEDVALUE(DimDate2[YYYYMM])

RETURN
SWITCH(
    TRUE(),
    _MatrixDate = _InventoryDate, 1,
    _MatrixDate > _InventoryDate, 0,
    _MatrixDate < _InventoryDate, 2,
    blank()
)

 

Result: 

Anett_R_0-1683529252510.png

 

Anonymous
Not applicable

The measure is correct. I think the data in your table doesn't fit the measure. Tip: try chatGPT if you search for answers.

Anonymous
Not applicable

The Inventorydate is written with a "i" and should be "I", this could be the problem.

thanks for your reply! Unfortunately, after correcting the "I" the calculation is still wrong.

Helpful resources

Announcements
Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors