Forum Discussion
3 different formulas with same outcome....why?
I have these measures :
Prod_ACT = [Actual] / [FTE_ACT]
Prod_Ytd_ACT = CALCULATE([Prod_ACT],DATESYTD(Dim_Date[Date]),VALUES(Dim_Date[Period]))
Prod_Ytd_ACT2 = TOTALYTD([Prod_ACT],Dim_Date[Date].[Date])
But they give me all 3 the same amount every month :
Why are the 2 with Ytd still breaking down on period?
I don't want them to break up to field Period.
I want them to add up all previous periods from that year.
rpinxt Try this:
Measure = VAR __Date = MAX(Dim_Date[Date]) VAR __Year = MAX('Dim_Date'[Year]) VAR __Table = SUMMARIZE( FILTER( ALL('Dim_Date'), [Date] <= __Date && [Year] = __Year ), [Period], "__Prod_ACT",[Prod_ACT] ) RETURN AVERAGEX(__Table,[__Prod_ACT])
9 Replies
rpinxt Eschewing a long diatribe about CALCULATE and TI functions, try this:
Measure = VAR __Period = MAX('Table'[Period]) VAR __Table = ADDCOLUMNS(FILTER('Table',[Period] <= __Period),"__Prod_ACT",[Prod_ACT]) RETURN SUMX(__Table,[__Prod_ACT])rpinxt
try thisProd_Ytd_ACT = VAR maxdate = MAX ( Dim_date[Date] ) RETURN CALCULATE ( [Prod_ACT], ALL ( Dim_date ), ISONORAFTER ( Dim_date[Date], maxdate, DESC ) )
BRGreg_Deckler Anonymous
Thanks guys for your help.
However no luck:Greg his formula still refuses to add up periods.
And the measure of dax_noob is doing somehting strange.
Changing DESC to ASC did not help it.
rpinxt Post sample source data as text please. It's just pure guesswork otherwise. This video may help though: https://youtu.be/meh3OkgFYfc
Sorry, having trouble following, can you post sample data as text and expected output?
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.Greg_Deckler I understand Greg. Made a simpler new model for myself to check the logics but there they work.....
It is a rather complicated (for me) model but I put in on Google Drive for you to have a look at.
Saw that that was also a possibility to share my file with you so here is the link:
https://drive.google.com/file/d/1vQAq2eifdjwul0bE6Un7OPQmfDvPpKd2/view?usp=sharing
And what I try to achieve is actually what is in the green column below :
