Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Running total dates too far on future or missing date

Hello,

 

I'm having trouble with running totals. My goal is to cut the DAX dates from the 2024 Q3 (red line), but dates go too far in future.

 

I'm having the following results with this DAX:

Measure = CALCULATE([Total sum],FILTER(ALL('Date'),'Date'[Date]<=MAX('Date'[date])))
Total sum = CALCULATE(SUM(FactTable[Value]),USERELATIONSHIP(FactTable[Date],'Date'[Date]))

 

If I try to use logic like this, I will miss the dates with blank values between the first and last dates, like this.

DAX: Measure = 

var _lastdate = CALCULATE(DATE(YEAR(MAX(FactTable[Date])),MONTH(MAX(FactTable[Date])),DAY(MAX(FactTable[Date]))),USERELATIONSHIP(FactTable[Date],'Date'[Date]))
return
IF(SELECTEDVALUE('Date'[Date])<=_lastdate,
CALCULATE([Total sum],FILTER(ALL('Date'),'Date'[Date]<=_lastdate))

 

 

My data is sorted like this:

1. Date table

1.1 Date table has dates from 1.1.2000 to 31.12.2049.

2. Fact table with inactive relationship with date table

2.1 Fact table has data for a singular date and value for each asset, which are due to complete in certain time in future.

 

Example:

Fact[Value]Fact[Date]
10001.5.2021
20001.8.2021
100001.7.2022
14591.1.2024

 

How do I overcome this problem?

 

Help is very much appreciated!

Help me please
Help Requested
  • Anonymous's avatar
    Anonymous
    5 years ago

    This does result in similar situation:

     

     

    HOWEVER!

     

    Total sum = CALCULATE(SUM(FactTable[Value]),USERELATIONSHIP(FactTable[Date],'Date'[Date]))

    Measure =
    var _max =maxx(allselected(FactTable), FactTable[Date])
    return
    CALCULATE([Total sum],FILTER(ALL('Date'),'Date'[Date]<=MAX('Date'[date]) && MAX('Date'[Date]) <= _max ))

     

    This did help me to overcome this problem! Thank you so much!!

     

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    Anonymous , Try like

     

    Total sum = CALCULATE(SUM(FactTable[Value]),USERELATIONSHIP(FactTable[Date],'Date'[Date]))

    Measure =
    var _max =maxx(allselected(FactTable), FactTable[Date])
    return
    CALCULATE([Total sum],FILTER(ALL('Date'),'Date'[Date]<=MAX('Date'[date]) && 'Date'[Date] <= _max ))

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      This does result in similar situation:

       

       

      HOWEVER!

       

      Total sum = CALCULATE(SUM(FactTable[Value]),USERELATIONSHIP(FactTable[Date],'Date'[Date]))

      Measure =
      var _max =maxx(allselected(FactTable), FactTable[Date])
      return
      CALCULATE([Total sum],FILTER(ALL('Date'),'Date'[Date]<=MAX('Date'[date]) && MAX('Date'[Date]) <= _max ))

       

      This did help me to overcome this problem! Thank you so much!!