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 (Expected column to show the value only when the Applicable Payroll Date changes.Many Thanks.AUD Total Daily EarningsCalculatedPayCalculation DateApplicable Payroll DateCalculated Pay (N)Calculated Pay (Expected)$327.81$2,031.341/09/2013 0:001/09/2013 0:002031.342031.34$178.60$0.004/09/2013 0:0015/09/2013 0:001959.180$185.73$0.005/09/2013 0:0015/09/2013 0:001959.180$162.02$0.006/09/2013 0:0015/09/2013 0:001959.180$228.94$0.007/09/2013 0:0015/09/2013 0:001959.180$292.16$0.008/09/2013 0:0015/09/2013 0:001959.180$151.95$0.0011/09/2013 0:0015/09/2013 0:001959.180$151.96$0.0012/09/2013 0:0015/09/2013 0:001959.180$151.96$0.0013/09/2013 0:0015/09/2013 0:001959.180$189.94$0.0014/09/2013 0:0015/09/2013 0:001959.180$265.92$1,959.1815/09/2013 0:0015/09/2013 0:001959.181959.18$141.82$0.0018/09/2013 0:0029/09/2013 0:002063.780$166.15$0.0019/09/2013 0:0029/09/2013 0:002063.780$271.50$0.0020/09/2013 0:0029/09/2013 0:002063.780$192.47$0.0021/09/2013 0:0029/09/2013 0:002063.780$269.46$0.0022/09/2013 0:0029/09/2013 0:002063.780$151.95$0.0025/09/2013 0:0029/09/2013 0:002063.780$151.96$0.0026/09/2013 0:0029/09/2013 0:002063.780$187.24$0.0027/09/2013 0:0029/09/2013 0:002063.780$221.65$0.0028/09/2013 0:0029/09/2013 0:002063.780$309.58$2,063.7829/09/2013 0:0029/09/2013 0:002063.782063.78$151.96$0.0030/09/2013 0:0013/10/2013 0:002167.230$171.93$0.001/10/2013 0:0013/10/2013 0:002167.230$151.95$0.002/10/2013 0:0013/10/2013 0:002167.230$289.32$0.005/10/2013 0:0013/10/2013 0:002167.230$333.89$0.006/10/2013 0:0013/10/2013 0:002167.230$405.20$0.007/10/2013 0:0013/10/2013 0:002167.230$151.96$0.008/10/2013 0:0013/10/2013 0:002167.230$141.82$0.009/10/2013 0:0013/10/2013 0:002167.230$187.85$0.0010/10/2013 0:0013/10/2013 0:002167.230$181.35$0.0011/10/2013 0:0013/10/2013 0:002167.232167.23$169.19$0.0014/10/2013 0:0027/10/2013 0:002036.550$184.08$0.0015/10/2013 0:0027/10/2013 0:002036.550$173.75$0.0018/10/2013 0:0027/10/2013 0:002036.550$247.18$0.0019/10/2013 0:0027/10/2013 0:002036.550$285.67$0.0020/10/2013 0:0027/10/2013 0:002036.550$151.96$0.0021/10/2013 0:0027/10/2013 0:002036.550$151.96$0.0022/10/2013 0:0027/10/2013 0:002036.550$151.96$0.0023/10/2013 0:0027/10/2013 0:002036.550$254.88$0.0026/10/2013 0:0027/10/2013 0:002036.550$265.92$2,036.5527/10/2013 0:0027/10/2013 0:002036.552036.55$202.35$0.0030/10/2013 0:0010/11/2013 0:001928.30$190.45$0.0031/10/2013 0:0010/11/2013 0:001928.30

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.

Forum Discussion

harshad_barge

Helper I

6 years ago

Solved

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 (Expected column to show the value only when the Applicable Payroll Date changes.
Many Thanks.

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

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

17 Replies

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.

Anonymous
Not applicable
6 years ago
I think your table is a table in the model, not a visual. So please use Power Query to do what you want. It's not only much much simpler but it IS THE WAY to do it.

Best
D

Forum Discussion

Help with DAX code

17 Replies

Related content

Missing Codes

User Group Leader Code of Conduct

DAX code issue

Error Code: 9518

Dax code optimization