Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Dear developers,
I tried to get a running total day by day , of the current month.
I used the dax methodology of adding A <= Max(A).
However it still does not show running total sum.
Could any master help?
Best regards,
Yuqi
Hello @Anonymous ,
Please try out below one. I put few comments to explain what is happening in each step.
I assume that there is a relationship between DateT and table storing values.
Measure =
//Calculate Range to be Aggregated
//Start off by taking latest date in selection this is also the day when calculation should end
var vLDate = max(DateT[CalendarDate])
//Get Start Date as 1st day of month - We will use it to aggregate values
var vStartDate = DATE(Year(vLDate),Month(vLDate),1)
Return // Now to main thing
CALCULATE(
Sum('OM Import link to P'[Turnover (value/+) (CUR)])
,
All(DateT) // Turn off any filtering on Your calendar table
,
DateT[CalendarDate] >=vStartDate // Fix calculation to sum always from 1st month day
,
DateT[CalendarDate] <=vLDate // Fix calculation to end sum on last day (selected one in chart)
)
@Anonymous Thanks for fast Reply!
I tried out your coding, the result is ONLY doing Sum , but not Running sum, however it at least minimize the date period as right range . (From current month start to yesterday)
I tried also other type of coding, it shows me the running sum result, but not right range.
it basically does not allow me to use the DateT date reference in this calculation(Gray out).
"DateT " table is linked to "Sellin Current Month CY" , using "Calendar Day" which is only text format , eg 20200201.
Best regards,
Yuqi
@Anonymous ,
Dont konw why that I am not allowed to insert text and picture at same time,
So I paste the picture sepearately above.
Hi @Anonymous ,
My piece may not work with SUMX (i am using SUM), but since you found working one than we can try best of both 🙂
Measure =
Var vLDate = TODAY()-1
var VMonthText =Value(FORMAT(vLDate,"YYYYMM") &"01") //PArse date to YearMonthText then add "01" and parse it back to number
Return
CALCULATE(
SUMX('Sellin Current Month CY','Sellin Current Month CY'[Turnover (Value/+) (CUR)]/1000000),
FILTER(ALLEXCEPT('Sellin Current Month CY','Sellin Current Month CY'[CalendarMonth]),
And(
'Selling Current Month CY'[Calendar Day]<=max('Sellin Current Month CY'[Calendar Day])
,
'Selling Current Month CY'[Calendar Day]>=VMonthText)
)
)
)
Hopefully it will work for you
Hi @Anonymous ,
I have changed from Sumx to SUm, it still shows the same..
BTW, tried out your new coding, it went error.
I even tried to map a column with real Date format, the result turned to be a strait line of Total sum.
But thanks for tring to help!
Best regards,
Yuqi
Hi @Anonymous
I tested this on PBI with some dummy data (unfortunatelly cannot se an option to send file. Maybe with below details it will work.
Data Model:
Results:
Formulas:
M01 =
//Calculate Range to be Aggregated
//Start off by taking latest date in selection this is also the day when calculation should end
var vLDate = Now()
Var vSDate = max(DateT[Calendar Day (Date)])
//Get Start Date as 1st day of month - We will use it to aggregate values
var vStartDate = DATE(Year(vLDate),Month(vLDate),1)
Return // Now to main thing
CALCULATE(
Sum('Sellin Current Month CV'[Turnover (Value/+) (CUR)])
,
All(DateT) // Turn off any filtering on Your calendar table
,
DateT[Calendar Day (Date)] >=vStartDate // Fix calculation to sum always from 1st month day
,
DateT[Calendar Day (Date)] <=vSDate // Fix calculation to end sum on last day (selected one in chart)
)
/////////////////////////////////////////////////////
M02 = //Calculate Range to be Aggregated
//Start off by taking latest date in selection this is also the day when calculation should end
var vLDate = FORMAT(Now(),"YYYYMMDD")
Var vSDate = max(DateT[Calendar Day]) // Capture which day is selected on chart
//Get Start Date as 1st day of month - We will use it to aggregate values
var vStartDate = Value((LEFT(vLDate,6)&"01"))
Return // Now to main thing
CALCULATE(
Sum('Sellin Current Month CV'[Turnover (Value/+) (CUR)])
,
All(DateT) // Turn off any filtering on Your calendar table
,
DateT[Calendar Day] >=vStartDate // Fix calculation to sum always from 1st month day
,
DateT[Calendar Day] <=vSDate // Fix calculation to end sum on last day (selected one in chart)
)
Hi @Anonymous , thanks for feedback.
I made a same data model, as you did. creating a seperate dataTest tabel with only two columns.
Result is still not working, maybe it is due to I have 1 to many relationship? (same day with different turnover records per regions. )
I could neither use the powerbi TotalMTD calculation, even I have linked "Calendar Day" with " Calendar Day (Date format)"
Hi @Anonymous
In the visual, could you please try using Calendar column from "DateTest" Table?
Thanks @Anonymous for the quick help,
That helped half way.
Hi @Anonymous
There are few ways to fix this:
Option 1 :
You can add additional Fliter to measure just after "DateT[Date]<=vSDate" add ",DateT[Date]<=vLDate" that will prevent measure to calculating past current date
Option 2:
You can modify a bit formula generating DateT Table. In End condition just ust Now(). Then calendar axis will not exceed current date, thus measure will not have anything co calculate
Option 3:
You can add relative date filter on visual like (Last 60 days with Today checkbox selected). Metric will still calculate further, but visual will display only last 60 days. Since Measure do not calculate past months it will remove those dimensions showing results for current month only.
Hi @Anonymous ,
For option 1, I would assume this is the easiest way, however it does not work. For option 2, It works only if you had one visual. But I need to show full month turnover data from last year same month on the visual at the same time, so this option does not help. For opthion 3, it does not help for same reason.
For option 1 , I had the coding as following
Measure test now =
Var vLDate=NOW()
Var vSdate=Max(DateT[Date])
VAR vStartDate = Date(year(vLDate),MONTH(vLDate),1)
Return
CALCULATE( SUM('Sellin Current Month CY'[Turnover (value/+) (CUR)])/1000000,
ALL(DateT),
DateT[Date] >= vStartDate,
DateT[Date] <= vSdate ,
DateT[Date] <= vLDate)
The Reuslt is still non-stoping, even though I added the line "DateT[Date] <= vLDate"
I would like to achieve some of the following graph. Gray line is running total turnover for Same month last year, per day. yellow line is running toal turnover for current month. 1-29 on axis are the days in current month.
BTW, which tool do you use to paste codings / pictures,
so it is not that large and looks nice and clean 🙂
Hi @Anonymous ,
For Last year you can just create another metric like below.
I am using "insert code" option when replying and set language to C++ 😉
Measure test now =
Var vLDate= Date(Year(NOW())-1,Month(now()),31)
Var vSdate= Max(DateT[Date])
Var vStartDate = Date(year(vLDate),MONTH(vLDate),1)
Return
CALCULATE( SUM('Sellin Current Month CY'[Turnover (value/+) (CUR)])/1000000,
ALL(DateT),
DateT[Date] >= vStartDate,
DateT[Date] <= vLDate)
Hi @Anonymous ,
Thanks for spending time on this .
I just want to inform, the following approch do not work.
Calculate ( xxx,
[Date] >=LDate,
[Date] <=LDate, xxx )
We need to restrict different Time dimention in order to achive my purpose.
I finally found the solution in the folloing post.
But thanks for trying and spending time on it,
Have a really good day!