Stop showing running total in yearly comparison graph

[Anonymous]

Hello dear community, 

I'm currently using the following graph to show the YoY progession of a running total: 

I would like that the running total dissapears for the future dates further "today's" date. I'm using this formula, since I want also that running total resets each year: 

CUMUL ANOMALIES = CALCULATE (
    SUM('dwh fac_CountAnomaliesPerMonthRespStatus'[ANOMALIES]), FILTER (
        ALL ('dwh dim_Date' ),
        'dwh dim_Date'[Date] <= MAX('dwh dim_Date'[Date])
        && 'dwh dim_Date'[Year] = MAX ('dwh dim_Date'[Year] )))

I appreciate any help on the subject. Thanks in advance 😄 !! 

[v-shex-msft]

Hi @Anonymous,

Can you please share some dummy data with a similar data structure and expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[Anonymous]

Hello, here is the data I'm using: 

Year	Month	ANOMALIES
2019	January	4
2019	February	1
2019	March	2
2019	April	1
2019	May	5
2019	June	3
2019	July	1
2019	August	3
2019	September	2
2019	October	5
2019	November	15
2019	December	1
2020	January	6
2020	February	4
2020	March	2
2020	April
2020	May	1
2020	June	3
2020	July	9
2020	August	2
2020	September	1
2020	October	3
2020	November	7
2020	December	1
2021	January	1
2021	February	5
2021	March	6
2021	April	4
2021	May
2021	June
2021	July
2021	August
2021	September
2021	October
2021	November
2021	December

If I plot the number of anomalies per month, I get the following, which is ok: 

However, when I create a running total formula to see the Yearly comparison, I get that my plot for 2020 is discontinuous in April, since there were no recorded anomalies on that month (as we can see in the data): 

Is there a way so the graph could continue in a constant line for april instead of having an empty space ? I'm using the following running total: 

CUMUL ANOMALIES = var today = TODAY()
return
if(max('dwh fac_CountAnomaliesPerMonthRespStatus'[DATE]) <= today , CALCULATE (
sum('dwh fac_CountAnomaliesPerMonthRespStatus'[ANOMALIES]), FILTER (
ALL ('dwh fac_CountAnomaliesPerMonthRespStatus'),
'dwh fac_CountAnomaliesPerMonthRespStatus'[DATE] <= max('dwh fac_CountAnomaliesPerMonthRespStatus'[DATE])
&& 'dwh fac_CountAnomaliesPerMonthRespStatus'[YEAR] = max('dwh fac_CountAnomaliesPerMonthRespStatus'[YEAR] ))))

Thank you so much for your help !

[v-shex-msft]

Hi @Anonymous,

I'd like to suggest you to move the condition into the calculation or it will replace the not matched calculation ranges to blank:

CUMUL ANOMALIES =
CALCULATE (
    SUM ( 'dwh fac_CountAnomaliesPerMonthRespStatus'[ANOMALIES] ),
    FILTER (
        ALL ( 'dwh fac_CountAnomaliesPerMonthRespStatus' ),
        'dwh fac_CountAnomaliesPerMonthRespStatus'[DATE]
            <= MAX ( 'dwh fac_CountAnomaliesPerMonthRespStatus'[DATE] )
            && 'dwh fac_CountAnomaliesPerMonthRespStatus'[YEAR]
                = MAX ( 'dwh fac_CountAnomaliesPerMonthRespStatus'[YEAR] )
            && 'dwh fac_CountAnomaliesPerMonthRespStatus'[DATE] <= TODAY ()
    )
)

Regards,

Xiaoxin Sheng

[Anonymous]

Hello, 

thanks for your help. Nevertheless, It still shows the blank on the graph.

Cheers, 

[v-shex-msft]

HI @Anonymous,

Can you please share a sample pbix file with some dummy data to test? It should help us clear why the formula not works.

Notice: please not attach sensitive data to it.

BTW, did you apply any filters on it? Its filter effect based on current row content, so it will change if you use a filter to break current row contents or replace the axis with other table categories. 

Regards,
Xiaoxin Sheng

[HarishKM]

@Anonymous  Hey ,YOu can use hasonevalue function to fulfil your requirement .

Measure = if(Hasonevalue([table[sales])<>Blank() , CALCULATE (
SUM('dwh fac_CountAnomaliesPerMonthRespStatus'[ANOMALIES]), FILTER (
ALL ('dwh dim_Date' ),
'dwh dim_Date'[Date] <= MAX('dwh dim_Date'[Date])
&& 'dwh dim_Date'[Year] = MAX ('dwh dim_Date'[Year] ))), Blank() )
Try that and let me know .

Kudos will be appriciated 

[HarishKM]

@Anonymous  Hey 

Try this 

 Test = if(SUM('dwh fac_CountAnomaliesPerMonthRespStatus'[ANOMALIES])<>0,CALCULATE (
SUM('dwh fac_CountAnomaliesPerMonthRespStatus'[ANOMALIES]), FILTER (
ALL ('dwh dim_Date' ),
'dwh dim_Date'[Date] <= MAX('dwh dim_Date'[Date])
&& 'dwh dim_Date'[Year] = MAX ('dwh dim_Date'[Year] ))), Blank() )

Kudos will be appriciated 

[Anonymous]

Hello again, 

same result. I'm guessin that if there is not variation of the value from one month to another, the line is not plotted then. 

[Anonymous]

Hello HarishKM,

Thanks for your answer. It works for the current year, however it is affecting past year values: 

Do you know the reason ? I'm using this: 

Measure = if(Hasonevalue('dwh fac_CountAnomaliesPerMonthRespStatus'[ANOMALIES])<>Blank() , CALCULATE (
SUM('dwh fac_CountAnomaliesPerMonthRespStatus'[ANOMALIES]), FILTER (
ALL ('dwh dim_Date' ),
'dwh dim_Date'[Date] <= MAX('dwh dim_Date'[Date])
&& 'dwh dim_Date'[Year] = MAX ('dwh dim_Date'[Year] ))), Blank() )

Thanks ! 

[amitchandak]

@Anonymous , refer these example code, how to control ,YTD based on Today

YTD QTY forced=
var _max = today()
return
if(max('Date'[Date])<=_max, calculate(Sum('order'[Qty]),DATESYTD('Date'[Date])), blank())
//or
//calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),filter('Date','Date'[Date]<=_max))
//calculate(TOTALYTD(Sum('order'[Qty]),'Date'[Date]),filter('Date','Date'[Date]<=_max))

LYTD QTY forced=
var _max = date(year(today())-1,month(today()),day(today()))
return
if(max('Date'[Date])<=_max, CALCULATE(Sum('order'[Qty]),DATESYTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max), blank())
//OR
//CALCULATE(Sum('order'[Qty]),DATESYTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max)
//TOTALYTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,year),'Date'[Date]<=_max)

[Anonymous]

Hello amitchandak, 

thank you so much for your quick answer, that helped me a lot! Thought, the graph is now showing until last month: 

Could you help me to show data until today? I'm using first option you proposed: 

YTD QTY forced = 
var _max = today()
return
if(max('dwh dim_Date'[Date])<=_max, calculate(Sum('dwh fac_CountAnomaliesPerMonthRespStatus'[ANOMALIES]), DATESYTD('dwh dim_Date'[Date].[Date])), blank())

Many thanks !