cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Power Participant

## 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.

1 ACCEPTED SOLUTION
Super User

@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])``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
9 REPLIES 9
Power Participant

@Greg_Deckler @Anonymous
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.

Super User

@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

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.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Power Participant

@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:

And what I try to achieve is actually what is in the green column below :

Super User

@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])``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Power Participant

@Greg_Deckler amazing....spot on 😁👍

Code looks not straight forward...what would be the bottleneck in my model causing all these troubles (because with standard formulas it would not work)?

Also just for my knowledge could it also return the YTD amount?

Probably it is there somewhere in the logic.

Super User

@rpinxt Should be to just use the same code and do a SUMX instead of an AVERAGEX.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Power Participant

Thanks! Learned a lot todayy 😁

Anonymous
Not applicable

@rpinxt

try this

``````Prod_Ytd_ACT =
VAR maxdate =
MAX ( Dim_date[Date] )
RETURN
CALCULATE (
[Prod_ACT],
ALL ( Dim_date ),
ISONORAFTER ( Dim_date[Date], maxdate, DESC )
)``````

BR

Super User

@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])``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Announcements