Help with DAX code

Hi, I have an excel output of a calculated DAX measure. The Calculated Pay (N) column is a sum of AUD Total Daily Earnings column, grouped by the Applicable Payroll Date. I want the Calculated Pay (...

Greg_Deckler

6 years ago

OK, the failure here was that your sample data was not representative of your actual data. In your actual data, we have to account for Employee_ID. So, that can be done like this:

Result = 
    VAR __CalculatedPay = 'Sheet1'[Calculated Pay (N)]
    VAR __Next = 
        MINX(
            FILTER(
                'Sheet1',
                'Sheet1'[Calculation Date] > EARLIER('Sheet1'[Calculation Date]) &&
                    'Sheet1'[Employee_ID] = EARLIER('Sheet1'[Employee_ID])
            ),
            'Sheet1'[Calculation Date]
        )
    VAR __NextPay = 
        MINX(
            FILTER(
                'Sheet1',
                'Sheet1'[Calculation Date] = __Next &&
                    'Sheet1'[Employee_ID] = EARLIER('Sheet1'[Employee_ID])
            ),
            'Sheet1'[Calculated Pay (N)]
        )
RETURN
    IF(__CalculatedPay <> __NextPay,__CalculatedPay,0)

Updated PBIX is attached.

DontPostTheSameThreadMultipleTimes.pbix918 KB

Greg_Deckler

Community Champion

6 years ago

OK, I may be mistaken but I believe you will need to add an Index column to your data table. Then you should be able to use a technique similar to MTBF as desribed here: See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586

harshad_barge

Helper I

6 years ago

So the excel formula works but in excel. How do I translate this logic to DAX?

=IF(AND(D2>=C2,E2<>E3),E2,0)

AUD Total Daily Earnings	CalculatedPay	Calculation Date	Applicable Payroll Date	Calculated Pay (N)	Calculated Pay (Expected)	Expected (Excel Formula)
$327.81	$2,031.34	1/09/2013 0:00	1/09/2013 0:00	2031.34	2031.34	2031.34
$178.60	$0.00	4/09/2013 0:00	15/09/2013 0:00	1959.18	0	0
$185.73	$0.00	5/09/2013 0:00	15/09/2013 0:00	1959.18	0	0
$162.02	$0.00	6/09/2013 0:00	15/09/2013 0:00	1959.18	0	0
$228.94	$0.00	7/09/2013 0:00	15/09/2013 0:00	1959.18	0	0
$292.16	$0.00	8/09/2013 0:00	15/09/2013 0:00	1959.18	0	0
$151.95	$0.00	11/09/2013 0:00	15/09/2013 0:00	1959.18	0	0
$151.96	$0.00	12/09/2013 0:00	15/09/2013 0:00	1959.18	0	0
$151.96	$0.00	13/09/2013 0:00	15/09/2013 0:00	1959.18	0	0
$189.94	$0.00	14/09/2013 0:00	15/09/2013 0:00	1959.18	0	0
$265.92	$1,959.18	15/09/2013 0:00	15/09/2013 0:00	1959.18	1959.18	1959.18
$141.82	$0.00	18/09/2013 0:00	29/09/2013 0:00	2063.78	0	0
$166.15	$0.00	19/09/2013 0:00	29/09/2013 0:00	2063.78	0	0
$271.50	$0.00	20/09/2013 0:00	29/09/2013 0:00	2063.78	0	0
$192.47	$0.00	21/09/2013 0:00	29/09/2013 0:00	2063.78	0	0
$269.46	$0.00	22/09/2013 0:00	29/09/2013 0:00	2063.78	0	0
$151.95	$0.00	25/09/2013 0:00	29/09/2013 0:00	2063.78	0	0
$151.96	$0.00	26/09/2013 0:00	29/09/2013 0:00	2063.78	0	0
$187.24	$0.00	27/09/2013 0:00	29/09/2013 0:00	2063.78	0	0
$221.65	$0.00	28/09/2013 0:00	29/09/2013 0:00	2063.78	0	0
$309.58	$2,063.78	29/09/2013 0:00	29/09/2013 0:00	2063.78	2063.78	2063.78
$151.96	$0.00	30/09/2013 0:00	13/10/2013 0:00	2167.23	0	0
$171.93	$0.00	1/10/2013 0:00	13/10/2013 0:00	2167.23	0	0
$151.95	$0.00	2/10/2013 0:00	13/10/2013 0:00	2167.23	0	0
$289.32	$0.00	5/10/2013 0:00	13/10/2013 0:00	2167.23	0	0
$333.89	$0.00	6/10/2013 0:00	13/10/2013 0:00	2167.23	0	0
$405.20	$0.00	7/10/2013 0:00	13/10/2013 0:00	2167.23	0	0
$151.96	$0.00	8/10/2013 0:00	13/10/2013 0:00	2167.23	0	0
$141.82	$0.00	9/10/2013 0:00	13/10/2013 0:00	2167.23	0	0
$187.85	$0.00	10/10/2013 0:00	13/10/2013 0:00	2167.23	0	0
$181.35	$0.00	11/10/2013 0:00	13/10/2013 0:00	2167.23	2167.23	2167.23
$169.19	$0.00	14/10/2013 0:00	27/10/2013 0:00	2036.55	0	0
$184.08	$0.00	15/10/2013 0:00	27/10/2013 0:00	2036.55	0	0
$173.75	$0.00	18/10/2013 0:00	27/10/2013 0:00	2036.55	0	0
$247.18	$0.00	19/10/2013 0:00	27/10/2013 0:00	2036.55	0	0
$285.67	$0.00	20/10/2013 0:00	27/10/2013 0:00	2036.55	0	0
$151.96	$0.00	21/10/2013 0:00	27/10/2013 0:00	2036.55	0	0
$151.96	$0.00	22/10/2013 0:00	27/10/2013 0:00	2036.55	0	0
$151.96	$0.00	23/10/2013 0:00	27/10/2013 0:00	2036.55	0	0
$254.88	$0.00	26/10/2013 0:00	27/10/2013 0:00	2036.55	0	0
$265.92	$2,036.55	27/10/2013 0:00	27/10/2013 0:00	2036.55	2036.55	2036.55
$202.35	$0.00	30/10/2013 0:00	10/11/2013 0:00	1928.3	0	0
$190.45	$0.00	31/10/2013 0:00	10/11/2013 0:00	1928.3	1928.3	1928.3

Greg_Deckler
Community Champion
6 years ago
Excel <> Power BI. Power BI cannot reference cells. But, pasting your Excel formula here might help. Power BI does not deal with cells, you have to filter your way to victory.
- harshad_barge
  Helper I
  6 years ago
  🙂
  Here is the formula:
  
  =IF(AND(D2>=C2,E2<>E3),E2,0)
  
  Checks if the column is greater than other however, it also checks if previous row is equal to the next row (in the same column) So that is the catch that I would use help figuring out in DAX.
  - Greg_Deckler
    Community Champion
    6 years ago
    Right, you will definitely want an index column. Otherwise, Power BI is going to have no idea if one row comes before another. If you have an index column, you can do something like this for a calculated column in a table:
    
    Column = VAR __NextID = [Index] + 1 VAR __NextDate = MAXX(FILTER('Table',[Index] = __NextID),[Applicable Payroll Date]) RETURN IF([Applicable Payroll Date] <> __NextDate,<<some calculation goes here>>,BLANK())

Forum Discussion

Help with DAX code

Related content

Missing Codes

User Group Leader Code of Conduct

DAX code issue

Error Code: 9518

Dax code optimization