Forum Discussion

Anonymous

Not applicable

3 years ago

Solved

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

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 ) = 1,
                SWITCH (
                    TRUE (),
                    [Service Period  (in months)]
                        - DATEDIFF ( [Sale Date], DATE ( YEAR ( [Sale Date] ), 12, 31 ), MONTH ) - 1 > 12, 12,
                    [Service Period  (in months)]
                        - DATEDIFF ( [Sale Date], DATE ( YEAR ( [Sale Date] ), 12, 31 ), MONTH ) - 1 <= 12
                        && [Service Period  (in months)]
                            - DATEDIFF ( [Sale Date], DATE ( YEAR ( [Sale Date] ), 12, 31 ), MONTH ) - 1 > 0,
                        [Service Period  (in months)]
                            - DATEDIFF ( [Sale Date], DATE ( YEAR ( [Sale Date] ), 12, 31 ), MONTH ) - 1,
                    0
                ),
            DATEDIFF ( [Sale Date], _a, YEAR ) = 2,
                SWITCH (
                    TRUE (),
                    [Service Period  (in months)]
                        - DATEDIFF ( [Sale Date], DATE ( YEAR ( [Sale Date] ) + 1, 12, 31 ), MONTH ) - 1 >= 12, 12,
                    [Service Period  (in months)]
                        - DATEDIFF ( [Sale Date], DATE ( YEAR ( [Sale Date] ) + 1, 12, 31 ), MONTH ) - 1 < 12
                        && [Service Period  (in months)]
                            - DATEDIFF ( [Sale Date], DATE ( YEAR ( [Sale Date] ), 12, 31 ), MONTH ) - 1 > 0,
                        [Service Period  (in months)]
                            - DATEDIFF ( [Sale Date], DATE ( YEAR ( [Sale Date] ) + 1, 12, 31 ), MONTH ) - 1,
                    0
                ),
            0
        )
            * DIVIDE ( [Total Sale], [Service Period  (in months)] )
    )
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.pbix81 KB

Anonymous

Not applicable

3 years ago

Thanks v-jianboli-msft This is wonderful
But i still have one last problem filtering
When i filter using Sale date , still the carry forward doesnt change it keeps calculating irresepective of filter

What should i change in the measure of carry forward which will make me use sale date as filter and help me calculate carry forward of only Sale ID's which were sold in selected dates in the filter

Anonymous

Not applicable

3 years ago

Please help v-jianboli-msft this is the last thing that is missing that is filtering using sale date for both carry forward and current year sale

other than that the answers are correct please after this I will accept the solution

I really appreciate your help v-jianboli-msft

Please help

v-jianboli-msft

Community Support

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

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