alculated Measure with Variable and if condition

[Raji001]

Hi All

@amitchandak  @Ritaf1983 @Ashish_Mathur  @Greg_Deckler 

I'm trying to write a dax query with the following requirement. I wanted to calculated the no. of pages used for each machine name. if the user didnot use the pages then he will show the same count for the next day. 

I have tried to write the formula like this but it didn't work. please help. 

Difference =

VAR CurrentMachine = PagesReport[MachineName]

VAR CurrentLicense = PagesReport[LicenseKey]

VAR CurrentPagesUsed = PagesReport[PagesUsed]

VAR PreviousPages =

   CALCULATE(

       MAX(PagesReport[PagesUsed]),

       FILTER(

           PagesReport,

           PagesReport[MachineName] = EARLIER(PagesReport[MachineName]) &&

           PagesReport[LicenseKey] = EARLIER(PagesReport[LicenseKey]) &&

           PagesReport[PagesUsed] <= EARLIER(PagesReport[PagesUsed])

       )

   )

RETURN

   IF(

       CurrentPagesUsed - PreviousPages > 0,

       CurrentPagesUsed - PreviousPages,

       CurrentPagesUsed

   )

 

DateReportRun	MachineName	LicenseKey	PagesUsed	Result	Explaination
2/5/2024	Ravi	DVAP	174	174	D2
2/6/2024	Ravi	DVAP	174	0	D3-D2
2/7/2024	Ravi	DVAP	174	0	D4-D3
2/8/2024	Ravi	DVAP	348	174	D5-D4
2/9/2024	Ravi	DVAP	696	522	D6-D5
2/12/2024	Ravi	DVAP	1069	547	D7-D6
2/17/2024	Ravi	DVAP	1069	0	D8-D7
2/19/2024	Ravi	DVAP	1069	0	D9-D8
2/26/2024	Ravi	DVAP	1152	83	D10-D9
2/27/2024	Ravi	DVAP	1536	384	D11-D10
2/28/2024	Ravi	DVAP	1536	0	D12-D11
2/29/2024	Ravi	DVAP	1536	0
3/1/2024	Ravi	DVAP	1536	0
3/4/2024	Ravi	DVAP	1536	0
1/8/2024	ABCABC	DVAP	135	135
1/9/2024	ABCABC	DVAP	135	0
1/23/2024	ABCABC	DVAP	479	344
1/24/2024	ABCABC	DVAP	826	347
1/25/2024	ABCABC	DVAP	1864	1038
1/26/2024	ABCABC	DVAP	2065	201
1/30/2024	ABCABC	DVAP	2806	741
1/31/2024	ABCABC	DVAP	2983	177
1/15/2024	YYYYYYYYYY	DVAP	88	88
1/17/2024	YYYYYYYYYY	DVAP	88	0
1/19/2024	YYYYYYYYYY	DVAP	88	0
3/11/2024	YYYYYYYYYY	DVAP	88	0
3/12/2024	YYYYYYYYYY	DVAP	88	0

[ryan_mayu · Accepted solution]

@Raji001 

pls try this

Column =

VAR _last=maxx(FILTER('Table','Table'[MachineName]=EARLIER('Table'[MachineName])&&'Table'[DateReportRun]<EARLIER('Table'[DateReportRun])),'Table'[DateReportRun])

return if(ISBLANK(_last),'Table'[PagesUsed],'Table'[PagesUsed]-maxx(FILTER('Table','Table'[DateReportRun]=_last&&'Table'[MachineName]=EARLIER('Table'[MachineName])),'Table'[PagesUsed]))

[rbangari001]

Thank you somuch. It worked. 

[ryan_mayu]

you are welcome