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 Community,
I have list of people and their revenue for some period. I need to calculate revenue for current and next quarter based on today per each person but it should not change when I select period in slicer.
I used calculation below and with "All" total amount is ok but per person it is wrong. Without all revenue per person is ok but it is setsitive to period in slicer and changes when selecting period.
Curr Q and curr Q+1 revenue =
CALCULATE(Revenue],
FILTER(ALL('dwh FactOpportunity'),
'dwh FactOpportunity'[closedate] >= STARTOFQUARTER('dwh DimDate'[Curr Q start]) &&
'dwh FactOpportunityt'[closedate] <= EOMONTH(TODAY(),3+MOD(3-MONTH(TODAY()),3))))
Solved! Go to Solution.
Hi @bsas
To calculate the revenue for each person you may try this measure.
Curr Q and curr Q+1 revenue =
CALCULATE (
[Revenue],
FILTER (
ALL ( 'dwh FactOpportunity' ),
'dwh FactOpportunity'[closedate]
>= STARTOFQUARTER ( 'dwh DimDate'[Curr Q start] )
&& 'dwh FactOpportunityt'[closedate]
<= EOMONTH ( TODAY (), 3 + MOD ( 3 - MONTH ( TODAY () ), 3 ) )
&& 'dwh FactOpportunity'[People] = MAX ( 'dwh FactOpportunity'[People] )
)
)
If this measure doesn't work, you can try my way.
Due to I don't know about your data model I build a sample to have a test.
My Sample Table:
I add a YearQtr column in this table to distinguish currenr qtr and next qtr.
My Measure:
Measure =
VAR _CurQtr = YEAR(TODAY())*100+QUARTER(TODAY())
VAR _NextQtr = MINX(FILTER(ALL('dwh FactOpportunity'),'dwh FactOpportunity'[YearQtr]>_CurQtr),'dwh FactOpportunity'[YearQtr])
Return
SUMX(FILTER(ALL('dwh FactOpportunity'),'dwh FactOpportunity'[YearQtr] in {_CurQtr,_NextQtr}&&'dwh FactOpportunity'[People]=MAX('dwh FactOpportunity'[People])),'dwh FactOpportunity'[Revenue])
Result is as below.
The slicer won't impact the result.
You can download the pbix file from this link: File
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @bsas
To calculate the revenue for each person you may try this measure.
Curr Q and curr Q+1 revenue =
CALCULATE (
[Revenue],
FILTER (
ALL ( 'dwh FactOpportunity' ),
'dwh FactOpportunity'[closedate]
>= STARTOFQUARTER ( 'dwh DimDate'[Curr Q start] )
&& 'dwh FactOpportunityt'[closedate]
<= EOMONTH ( TODAY (), 3 + MOD ( 3 - MONTH ( TODAY () ), 3 ) )
&& 'dwh FactOpportunity'[People] = MAX ( 'dwh FactOpportunity'[People] )
)
)
If this measure doesn't work, you can try my way.
Due to I don't know about your data model I build a sample to have a test.
My Sample Table:
I add a YearQtr column in this table to distinguish currenr qtr and next qtr.
My Measure:
Measure =
VAR _CurQtr = YEAR(TODAY())*100+QUARTER(TODAY())
VAR _NextQtr = MINX(FILTER(ALL('dwh FactOpportunity'),'dwh FactOpportunity'[YearQtr]>_CurQtr),'dwh FactOpportunity'[YearQtr])
Return
SUMX(FILTER(ALL('dwh FactOpportunity'),'dwh FactOpportunity'[YearQtr] in {_CurQtr,_NextQtr}&&'dwh FactOpportunity'[People]=MAX('dwh FactOpportunity'[People])),'dwh FactOpportunity'[Revenue])
Result is as below.
The slicer won't impact the result.
You can download the pbix file from this link: File
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @bsas , a best practice is to apply ALL() to specific column(s) in order to keep other filters on the same table effective; you might want tweak your measure this way,
Curr Q and curr Q+1 revenue =
CALCULATE (
[Revenue],
FILTER (
ALL ( 'dwh FactOpportunity[closedate]' ),
'dwh FactOpportunity'[closedate]
>= STARTOFQUARTER ( 'dwh DimDate'[Curr Q start] )
&& 'dwh FactOpportunityt'[closedate]
<= EOMONTH ( TODAY (), 3 + MOD ( 3 - MONTH ( TODAY () ), 3 ) )
)
)
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Thanks but this don't work for my case
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 | |
97 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |