Forum Discussion

_power_bi's avatar
_power_bi
Helper I
2 years ago
Solved

DAX question using two date columns and filtering

I am currently pulling blanks when I know there is data. Something is wrong with my DAX but I'm not sure. Any help is appreciated

 

I need a running total of the transfers[quantity] field to put in a line chart by week or month. The xaxis on the line chart is a date from the dim_date table. 

 

date dimension= dim_date[date]

fact table= transfers

fact table date field= transfers[due_date] : this has an inactive relationship to the date dimension

 

I need to filter my transfers table to show the sum of transfers[quantity] (not YTD, but running total).

Filters:  transfers[direction]= "out"

simplefilter= 0

I must be able to filter using active relationships to my dim_facility table and dim_device table. 

VAR MaxDate = MAX('dim_date'[date])
VAR MinDate = Min('dim_date'[date])
RETURN
  CALCULATE(
    SUM('Transfers'[QUANTITY]),
    'Transfers'[Direction] = "Out",
    USERELATIONSHIP(Dim_Date[Date], 'Transfers'[Due_Date]),
    FILTER(
        ALLEXCEPT('Transfers', dim_Facility[contract]),
        'Transfers'[Due_Date] <= MaxDate
        && 'Transfers'[Due_Date] >= MinDate
        && 'Transfers'[SimpleFilter] = 0
    )
)

 

Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    2 years ago

    Hi,

    Does this measure work?

    =CALCULATE(SUM('Transfers'[QUANTITY]),'Transfers'[Direction] = "Out",'Transfers'[SimpleFilter] = 0,USERELATIONSHIP(Dim_Date[Date], 'Transfers'[Due_Date]),datesbetween(Dim_Date[Date],minx(all(Dim_Date),Dim_Date[Date]),max(Dim_Date[Date])))

2 Replies

  • parry2k's avatar
    parry2k
    Super User
    2 years ago

    _power_bi not sure what you are trying to do but try this measure and go from there

     

      CALCULATE(
    SUM('Transfers'[QUANTITY]),
    'Transfers'[Direction] = "Out",
    'Transfers'[SimpleFilter] = 0,
    USERELATIONSHIP(Dim_Date[Date], 'Transfers'[Due_Date]),
    FILTER(
        ALL ( Dim_Date[Date] ), 
        Dim_Date[Date] <= MAX ( Dim_Date[Date] )
     
    )
)
  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    2 years ago

    Hi,

    Does this measure work?

    =CALCULATE(SUM('Transfers'[QUANTITY]),'Transfers'[Direction] = "Out",'Transfers'[SimpleFilter] = 0,USERELATIONSHIP(Dim_Date[Date], 'Transfers'[Due_Date]),datesbetween(Dim_Date[Date],minx(all(Dim_Date),Dim_Date[Date]),max(Dim_Date[Date])))