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.
Hi all, I have the following visualizations regarding cumulative Purchase Orders:
Due to there are no data for June in 2020, I need that the cumulative of June should be completed with the cumulative of the previous month. I'm using the following measure for "2020":
2020 = if(isblank([Monto YTD]),blank(),CALCULATE([Monto YTD] , DATESYTD(Calendario[Date])))
whrere:
* Monto YTD = SUM(PurchaseOrder[MontoUSD])
* Calendario = Calendar Table
Thanks in advance.
Joao
Solved! Go to Solution.
@JoaoMS , Try like
2020 = if(isblank([Monto YTD]) && max(Calendario[Date]) >today(),blank() ,CALCULATE([Monto YTD] , DATESYTD(Calendario[Date])))
Power bi rolled over per month with blank months
Good afternoon
Please, could you help me, I have companies with data up to the month of October ( 10) , when segment in month 10 , only Development appears. How do I make culture appear with its last accumulated of June (6)
@JoaoMS , then why are using isblank, remove that and try
2020 = CALCULATE([Monto YTD] , DATESYTD(Calendario[Date]))
This will give data in June
for that put a filter like this and try
My data example
YTD QTY forced=
var _max = today()
return
calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),'Date'[Date]<=_max)
//calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),filter('Date','Date'[Date]<=_max))
or
YTD QTY forced=
var _max = maxx('order',[Order date])
return
calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),'Date'[Date]<=_max)
//calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),filter('Date','Date'[Date]<=_max))
Commented is alternate option
Ideally, we should relative/advance filter , less than today
@JoaoMS , Try like
2020 = if(isblank([Monto YTD]) && max(Calendario[Date]) >today(),blank() ,CALCULATE([Monto YTD] , DATESYTD(Calendario[Date])))
You Sir are my new hero. I have been seaching the forums and youtube channels for 5 days and tryed about 5 different measures and nothing fixed the line graph gap issue in a running sum or count for when a value in a row is blank. And then I found your solution which solved the problem. I would have thought that this is a relative common issue when wotking with cummulated values. I am super greatful to you Sir.
Perfect, it works! Thank you
@JoaoMS - Sorry, what is the question?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
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 |
---|---|
110 | |
99 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |