Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 😄 !!
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
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 !
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
Hello,
thanks for your help. Nevertheless, It still shows the blank on the graph.
Cheers,
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
@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
@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
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.
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 !
@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)
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 !
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |