Forum Discussion

Raji001's avatar
Raji001
New Member
2 years ago
Solved

alculated Measure with Variable and if condition

 
 

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
   )

 
DateReportRunMachineNameLicenseKeyPagesUsedResultExplaination
2/5/2024RaviDVAP174174D2
2/6/2024RaviDVAP1740D3-D2
2/7/2024RaviDVAP1740D4-D3
2/8/2024RaviDVAP348174D5-D4
2/9/2024RaviDVAP696522D6-D5
2/12/2024RaviDVAP1069547D7-D6
2/17/2024RaviDVAP10690D8-D7
2/19/2024RaviDVAP10690D9-D8
2/26/2024RaviDVAP115283D10-D9
2/27/2024RaviDVAP1536384D11-D10
2/28/2024RaviDVAP15360D12-D11
2/29/2024RaviDVAP15360 
3/1/2024RaviDVAP15360 
3/4/2024RaviDVAP15360 
1/8/2024ABCABCDVAP135135 
1/9/2024ABCABCDVAP1350 
1/23/2024ABCABCDVAP479344 
1/24/2024ABCABCDVAP826347 
1/25/2024ABCABCDVAP18641038 
1/26/2024ABCABCDVAP2065201 
1/30/2024ABCABCDVAP2806741 
1/31/2024ABCABCDVAP2983177 
1/15/2024YYYYYYYYYYDVAP8888 
1/17/2024YYYYYYYYYYDVAP880 
1/19/2024YYYYYYYYYYDVAP880 
3/11/2024YYYYYYYYYYDVAP880 
3/12/2024YYYYYYYYYYDVAP880 
Need Help
  • ryan_mayu's avatar
    ryan_mayu
    2 years ago

    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]))
     

3 Replies

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    2 years ago

    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]))