Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Cumulative line chart break

Hello, I'm trying to make a cumulative chart with this code: YTD GP = CALCULATE( SUM(BIPER[GP]), FILTER( ALLSELECTED(BIPER), BIPER[Invoice Date] <= MAX(BIPER[Invoice Date]...
Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    6 years ago

    Actually I already has date dimension. The reason I use the current table's date is because I designed the goal for every working day at my company. So if I use the date dimension, the line for Goal will break. 

    I already find the solution and it works. So here's my new measure:

     

    YTD GP = 
IF(MAX('CALENDAR'[Date])>TODAY(), BLANK(), 
    IF(MAX(ST[Date])=BLANK(), BLANK(),
        CALCULATE(
            SUM(BIPER[GP]),
            FILTER(
                ALLSELECTED('CALENDAR'),
                'CALENDAR'[Date] <= MAX('CALENDAR'[Date])
            )
        )
    )
)

     

    Anyway, thank you parry2k !

parry2k's avatar
parry2k
Icon for Super User rankSuper User
6 years ago

Anonymous add +0 to your measure

 

try this
 
Same Business Day Sales PY = 
VAR __currentBusinessDay = MAX ( Calendar[BusinessDay] )
RETURN
CALCULATE (
SUM ( Table[Sales] ),
DATEADD ( Calendar[Date], -1, YEAR ),
Calendar[BusinessDay] = __currentBusinessDay
) + 0
Anonymous's avatar
Anonymous
Not applicable
6 years ago

It gets worse

 

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    6 years ago

    Anonymous i didn't notice that you are not using date dimension. It is recommended to add date dimension and use that for this calculation instead of using date from your transaction table. there are many posts on how to add date dimension in the model.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Actually I already has date dimension. The reason I use the current table's date is because I designed the goal for every working day at my company. So if I use the date dimension, the line for Goal will break. 

      I already find the solution and it works. So here's my new measure:

       

      YTD GP = 
IF(MAX('CALENDAR'[Date])>TODAY(), BLANK(), 
    IF(MAX(ST[Date])=BLANK(), BLANK(),
        CALCULATE(
            SUM(BIPER[GP]),
            FILTER(
                ALLSELECTED('CALENDAR'),
                'CALENDAR'[Date] <= MAX('CALENDAR'[Date])
            )
        )
    )
)

       

      Anyway, thank you parry2k !