Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Filtering based on other date

Hi All,

 

I have an issue with regards on the date slicer that i'm trying to slice with. 

 

The ER diagram

 

Dim_date --> Invoice date (active)

 

Dim_date --> Provision date (inactive)

Provision Date Dax 

Provision Date = CALCULATE (
MAX ( Overall[DateIn] ),
FILTER (
ALL ( Overall ),
Overall[Provision] <> BLANK ()
&& Overall[DateIn] <= EARLIER ( Overall[DateIn] )
&& Overall[CHASSIS] = EARLIER( Overall[CHASSIS])
&& Overall[Category] = "Provision"
)
)
 
Utilisation based on contract date =
CALCULATE(sum(Overall[Closing Balance]),
USERELATIONSHIP(Dim_Calender[Date],Overall[Provision Date]),
Overall[Category] = "utilisation")
 
 
I have 2 different matrix date that show the balance differently. 1 matrix is to compare based on the provision date and the other is based onthe invoice date.
 
If the date slicer is run as per the latest date, we will be able to have the same balances 

 

The question is how can i filter the transaction to only filter based on the Invoice date: i have filter to 12/Oct/2021 but the 13/Oct/2021 record still appear in the report

 

I have included the PBIX file as per below.
Need Help
Tutorial Requests

5 Replies

  • smpa01's avatar
    smpa01
    Community Champion
    4 years ago

    Anonymous  to achieve what you need, instead of activating the USERELATIONSHIP , you can write a following measure

    _Utilisation based on contract date =
VAR _selectedDate =
    ALLSELECTED ( Dim_Calender[Date] )
VAR _sum =
    CALCULATE (
        SUM ( Overall[Closing Balance] ),
        FILTER ( Overall, Overall[Category] = "utilisation" ),
        TREATAS ( _selectedDate, Overall[Invoice Date] )
    )
RETURN
    _sum

     

     

     

     

     

    • smpa01's avatar
      smpa01
      Community Champion
      4 years ago

      Anonymous  did you have a chance to try out the above yet?

  • v-jingzhang's avatar
    v-jingzhang
    Community Support
    4 years ago

    Hi Anonymous

     

    When you calculate the Utilisation based on contract date, on the invoice date 10/13/2021, it is filtered and calculated according to Provision Date 11/22/2019, so the result will be -355 rather than blank. The table visual will show all non-blank values.

     

    You can add an IF condition to decide whether to calculate the Utilisation based on contract date by comparing to invoice date. But this would bring another problem that the Total value is not the sum of displayed ones in the column. Although we know that the total value is correct when calculating based on contract date, this may be a little confusing to other report users. 

    Measure = IF(ISBLANK(MAX(Overall[Invoice Date])),BLANK(),
CALCULATE(sum(Overall[Closing Balance]),
USERELATIONSHIP(Dim_Calender[Date],Overall[Provision Date]),
Overall[Category] = "utilisation"))

     

    Or you can add another measure to get the total of displayed values in the column. But the total is not correct based on the contract date. 

    Measure 2 = SUMX(VALUES(Overall[Invoice Date]),[Measure])

     

    Best Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as Solution to help other members find it.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Have tried both the measure is there a measure or DAX that can achieve both the transaction and showing the total the correct amount? 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Bumping this up. the formula that was provided are not displaying the correct figure based on the datein. Any guru able to provide any input?