Measures in Power BI Calculation

Hello All , This is a question which has been a problem for atleast a month . I am using Power BI for more than a year. But I am stuck here and any help will be highly appreciated Following is the ...

Need Help

Show and Tell

v-jianboli-msft

3 years ago

Hi Anonymous ,

Please try:

Carry forward Sale2 = 
VAR _a =
    MAXX ( 'Calendar', [Date] )
VAR _b =
    MINX ( 'Calendar', [Date] )
VAR _c =
    SELECTCOLUMNS ( 'Table', "State", [State] )
var _e = SELECTCOLUMNS('Table',"Date",[Sale Date])   
VAR _d =
    SUMX (
        FILTER ( ALL ( 'Table' ), [State] IN _c && [Sale Date] <= _a &&[Sale Date] in _e),
        SWITCH (
            TRUE (),
            NOT([Sale Date] in SELECTCOLUMNS('Table',"Date",[Sale Date])),0,
            DATEDIFF ( [Sale Date], _a, YEAR ) >= 2, 0,
            DATEDIFF ( [Sale Date], _a, YEAR ) <= 0, 0,
            DATEDIFF ( [Sale Date], _b, MONTH ) <= [Service Period  (in months)],
                DIVIDE ( [Total Sale], [Service Period  (in months)] )
                    * ( [Service Period  (in months)] - DATEDIFF ( [Sale Date], _b, MONTH ) ),
            0
        )
    )
RETURN
    _d

Final output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Measures in Power BI Calculation.pbix82 KB

Anonymous
3 years ago
I made some changes to and this works perfectly

Carry forward Avoidance =
VAR _a =
MAXX ( 'Calendar', [Date] )
VAR _b =
MINX ( 'Calendar', [Date] )
VAR _c =
SELECTCOLUMNS ( 'Data', "Catg", [Catg] )
var _e = SELECTCOLUMNS('Data',"Date",[SaleDate])
VAR _d =
SUMX (
FILTER ( ALL ( 'Data' ), [Catg] IN _c && [SaleDate] <= _a && Data[SaleDate] in _e ),
SWITCH (
TRUE (),
NOT([Saledate] in SELECTCOLUMNS('Data',"Date",[Saledate])),0,
DATEDIFF ( [Saledate], _a, YEAR ) = 1,
SWITCH (
TRUE (),

(DATEDIFF(Data[Saledate],DATE(YEAR(Data[Saledate]),12,31),MONTH)+1 ) < Data[ServiceMonths]
&&
(DATEDIFF(Data[Saledate],DATE(YEAR(Data[Saledate]),12,31),MONTH)+1 ) + 12 >= Data[ServiceMonths]
&&
Data[ServiceMonths] - (DATEDIFF(Data[Saledate],DATE(YEAR(Data[Saledate]),12,31),MONTH)+1 ) <= 12
,
Data[ServiceMonths] - (DATEDIFF(Data[Saledate],DATE(YEAR(Data[Saledate]),12,31),MONTH)+1 ),
(DATEDIFF(Data[Saledate],DATE(YEAR(Data[Saledate]),12,31),MONTH)+1 ) < Data[ServiceMonths]
&&
(DATEDIFF(Data[Saledate],DATE(YEAR(Data[Saledate]),12,31),MONTH)+1 ) + 12 >= Data[ServiceMonths]
&&
Data[ServiceMonths] - (DATEDIFF(Data[Saledate],DATE(YEAR(Data[Saledate]),12,31),MONTH)+1 ) >12
,
12,
(DATEDIFF(Data[Saledate],DATE(YEAR(Data[Saledate]),12,31),MONTH)+1) + 12 < Data[ServiceMonths] , 12 ,
(DATEDIFF(Data[Saledate],DATE(YEAR(Data[Saledate]),12,31),MONTH)+1) >= Data[ServiceMonths] , 0 ,0)
,
DATEDIFF ( [Saledate], _a, YEAR ) = 2,
SWITCH (
TRUE (),

(DATEDIFF(Data[Saledate],DATE(YEAR(Data[Saledate]),12,31),MONTH)+1)+12 < Data[ServiceMonths]
&&
(DATEDIFF(Data[Saledate],DATE(YEAR(Data[Saledate]),12,31),MONTH)+1)+12 + 12 <= Data[ServiceMonths]
, 12,
(DATEDIFF(Data[Saledate],DATE(YEAR(Data[Saledate]),12,31),MONTH)+1)+12 < Data[ServiceMonths]
&&
(DATEDIFF(Data[Saledate],DATE(YEAR(Data[Saledate]),12,31),MONTH)+1)+12 + 12 >= Data[ServiceMonths]

, Data[ServiceMonths]-((DATEDIFF(Data[Saledate],DATE(YEAR(Data[Saledate]),12,31),MONTH)+1)+12 ),
(DATEDIFF(Data[Saledate],DATE(YEAR(Data[Saledate]),12,31),MONTH)+1)>=Data[ServiceMonths],0,0),0)
* DIVIDE(Data[AvoidanceSale],Data[ServiceMonths]))
return _d

Current year Avoidance sale =
VAR _a =
MAXX ( 'Calendar', [Date] )
VAR _b =
SUMX (
FILTER('Contracts',Contracts[Saledate]<=_a&&Contracts[Saledate]>=MINX('Calendar',[Date])),
DIVIDE ( Contracts[AvoidanceSale], [Service Months] )
* (
MINX (
{ DATEDIFF ( 'Contracts'[Saledate], _a, MONTH ), [Service Months] },
[Value]
)
+ IF (
MINX (
{ DATEDIFF ( 'Contracts'[Saledate], _a, MONTH ), [Service Months] },
[Value]
) = [Service Months],
0,
1
)
)
)
RETURN
_b

Anonymous

Not applicable

3 years ago

Hello v-jianboli-msft
I have tried manual calculation to see which measure is wrong

This is measure that you helped me with

This is manual calculation (these numbers should match)

for B state
2020 - Current Year - 174.6
2020 - Carry forward - 0
2021 - Current Year -388
2021 - Carry Forward - 116.4

Hence when I can see Current Year sale is wrong as number for 2021 doesnt match for B when tried with your measure and manually in excel

Anonymous

Not applicable

3 years ago

v-jianboli-msft
I changed your measure of current year sale to followinf

Current year Sale =
VAR _a = MAXX ( 'Calendar', [Date] )

VAR _b = SUMX (
'Table',
DIVIDE ( [Total Sale], [Service Period (in months)] )
* (
MINX (
{ DATEDIFF ( 'Table'[Sale Date], _a, MONTH ), [Service Period (in months)] },
[Value]
) + IF(MINX (
{ DATEDIFF ( 'Table'[Sale Date], _a, MONTH ), [Service Period (in months)] },
[Value]
) = [Service Period (in months)], 0, 1)
)
)

RETURN _b

And now everything is perfect

One last help with same measure

Why is column level total not working for carry forward 😞

v-jianboli-msft
Community Support
3 years ago
Hi Anonymous ,

Please try:

Carry forward Sale = VAR _a = MAXX ( 'Calendar', [Date] ) VAR _b = MINX ( 'Calendar', [Date] ) VAR _c = SELECTCOLUMNS ( 'Table', "State", [State] ) VAR _d = SUMX ( FILTER ( ALL ( 'Table' ), [State] IN _c && [Sale Date] <= _a ), SWITCH ( TRUE (), DATEDIFF ( [Sale Date], _a, YEAR ) >= 2, 0, DATEDIFF ( [Sale Date], _a, YEAR ) <= 0, 0, DATEDIFF ( [Sale Date], _b, MONTH ) <= [Service Period (in months)], DIVIDE ( [Total Sale], [Service Period (in months)] ) * ( [Service Period (in months)] - DATEDIFF ( [Sale Date], _b, MONTH ) ), 0 ) ) RETURN _d

Final output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Measures in Power BI Calculation.pbix70 KB
- Anonymous
  Not applicable
  3 years ago
  Hello v-jianboli-msft
  Thank you so much
  
  The Current year sale should stop at the year of last sale date (in my actual official data its 2023, hence current year for 2024 and 2025 will not show up )
  BUt carry forward sale should show maximum upto 2 years
  As carry forward is for years after the year it was sold , but using your carry forward sale measure , it stops calculating on year 2023 , please can you help so that it can go upto two years or service period which ever is minimum for that sale ID
  
  I really appreciate your help

Forum Discussion

Measures in Power BI Calculation

Related content

DAX Calculated Measure

POWER BI DAX MEASURE

DAX Measure for Ranking the valid rows and calculating the average rating

power bi measure

Measure or Calculated Column