Forum Discussion

Caz_16's avatar
Caz_16
Icon for Helper II rankHelper II
5 years ago
Solved

Cumulative Calculation to Date

Hi all,    So I found this great formula on the forum for calculating a measure for FYTD, and then having the lines stop and not continue on until the end of the calendar year. However, there is on...
DAX
Need Help
  • Caz_16's avatar
    Caz_16
    5 years ago

    Anonymous ,

     

    I can see what you were doing, working with only variables, but unfortunately It produced the same thing that my previous formula did. However, you did set me on track for what I was looking for. I had to replace the ALL(TIME[Entry Date]) with ALLCROSSFILTERED(TIME[Entry Date]), because the ALL was still enforcing the filtering coming from the outside table. ALLCROSSFILTERED got rid of those filters coming from the table where I keep my Time Entry Categories. So my final formula wound up being:

     

    Cumulative Cost by Day = 

VAR LastDate1 = CALCULATE(LASTDATE('TIME'[EntryDate]),ALLCROSSFILTERED('TIME'))

RETURN
IF(
    SELECTEDVALUE('Calendar Table'[Date])> LastDate1,
    BLANK(),
    CALCULATE(
        SUM('TIME'[COST]),
    FILTER(ALLSELECTED('Calendar Table'[Date]),
        'Calendar Table'[Date] <= MAX('Calendar Table'[Date])
        )
    )
)

     

     

    Thank you for your time and reply. 

Anonymous's avatar
Anonymous
Not applicable
5 years ago
If removing all filters with ALL does not work, it means somewhere in your code you must have a bidirectional filter(s) enabled. Is this correct? If not, would you please care to explain why ALL did not work whereas ALLCROSSFILTERED did? Thanks.
Caz_16's avatar
Caz_16
Icon for Helper II rankHelper II
5 years ago

Anonymous 

 

Yes I have Bidirectional filters enabled for the Calendar table that Im trying to filter. ALLCROSSFILTERED removes all of the filters which are being applied across both tables, which results in the measure evaluating the measure for all of the rows in CalendarTable[Date], and not the rows specific to my Time Entry Category. See the documentation on ALLCROSSFILTERED for a good example. 

 

https://docs.microsoft.com/en-us/dax/allcrossfiltered-function-dax

 

Caz