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 All, Cannot figure out how to write a measure for this?
I have data as follows
Date InvAmt SalesAmt 20180821 1000 1500
20180814 1000 400 20180807 1000 600 20180801 1000 1100
20180724 1000 1500
I need a measure called NegativeWeek that gives me the date where the running remainder of InvAmt - SalesAmt starting from the current row went <= 0
e.g. For 20180814 InvAmt-SalesAmt = 600, we start here with 600 as Remainder, we calculate 20180807, we do 600 remainder - 600 (SalesAmt on this row) = 0. So for 201808014, NegativeWeek = 20180807
For 20180807, InvAmt - SalesAmt = 400, we start here with 400 as Remainder, we calculate 20180801, we do 400 remainder - 1100 (SalesAmt on this row) = -700, So for 20180807, NegativeWeek = 20180801 Hope i made this clear, the dates are descending (we start from a date and calculate remainder going back).
I cannot do this as calculated columns since the remainderAmt is dynamic based on which week we start from.
Any help? Thanks
Solved! Go to Solution.
HI @bi_quest18,
You can take a look at following calculate column formulas to find out rolling remainder:
Non Zero = IF ( [InvAmt] - [SalesAmt] <= 0, BLANK (), [Index] ) Rolling Remainder = VAR rangeStart = IF ( [Non Zero] <> BLANK (), CALCULATE ( MAX ( 'Original'[Index] ) + 1, FILTER ( ALL ( 'Original' ), [Index] <= EARLIER ( [Index] ) && [Non Zero] = BLANK () ) ) ) VAR rangeEnd = IF ( [Non Zero] <> BLANK (), CALCULATE ( MIN ( 'Original'[Index] ) - 1, FILTER ( ALL ( 'Original' ), [Index] >= EARLIER ( [Index] ) && [Non Zero] = BLANK () ) ) ) VAR filtered = FILTER ( ALL ( 'Original' ), [Index] >= rangeStart && [Index] <= rangeEnd ) RETURN IF ( [Non Zero] <> BLANK (), [InvAmt] - SUMX ( FILTER ( filtered, [Index] <= EARLIER ( [Index] ) ), [SalesAmt] ) )
Test sample:
Notice:
1. Your date column need to write complex convert formula to find out nearest next/previous records, so I add index column in query editor for looping through table.
2. 'Non Zero' column is used to ignore records who not suitable for your conditions.
3. I attached sample pbix file below for reference.
Regards,
Xiaoxin Sheng
HI @bi_quest18,
You can take a look at following calculate column formulas to find out rolling remainder:
Non Zero = IF ( [InvAmt] - [SalesAmt] <= 0, BLANK (), [Index] ) Rolling Remainder = VAR rangeStart = IF ( [Non Zero] <> BLANK (), CALCULATE ( MAX ( 'Original'[Index] ) + 1, FILTER ( ALL ( 'Original' ), [Index] <= EARLIER ( [Index] ) && [Non Zero] = BLANK () ) ) ) VAR rangeEnd = IF ( [Non Zero] <> BLANK (), CALCULATE ( MIN ( 'Original'[Index] ) - 1, FILTER ( ALL ( 'Original' ), [Index] >= EARLIER ( [Index] ) && [Non Zero] = BLANK () ) ) ) VAR filtered = FILTER ( ALL ( 'Original' ), [Index] >= rangeStart && [Index] <= rangeEnd ) RETURN IF ( [Non Zero] <> BLANK (), [InvAmt] - SUMX ( FILTER ( filtered, [Index] <= EARLIER ( [Index] ) ), [SalesAmt] ) )
Test sample:
Notice:
1. Your date column need to write complex convert formula to find out nearest next/previous records, so I add index column in query editor for looping through table.
2. 'Non Zero' column is used to ignore records who not suitable for your conditions.
3. I attached sample pbix file below for reference.
Regards,
Xiaoxin Sheng
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 |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |