Calculate max date value from date range

[mabdelrahman76]

Hi,

I have tracking table called "test", i am trying to to display only the most recent values for each group in column “CD_Trading_Id” regarding the value of the max Date modified.

1. I have Date slicer to to filter the most data values for each group ( in my example column “CD_Trading_Id”)
2. I would like to display only the value for each group, then sum the values for all group regarding from the previous measure, of course I don’t want to show the values for the non most recent date for each group.
3. I created two measure, 1st measure to calculate the values of most recent date for each group “CD_Trading_Id” , 2nd measure to sum the total for all group.
   measure 1:
   
   MostvalueByGrp =var maxdate = CALCULATE(max(Test[Modified]),VALUES(Test[CD_Trading_Id]))

   return CALCULATE(SELECTEDVALUE(Test[No. of Cases]),Test[Modified]=maxdate)
   Measure 2:
   
   Sum =var _table = SUMMARIZE(Test,Test[CD_Trading_Id], "_value", [MostvalueByGrp])

   return SUMX(_table, [_value])
   
   
   

    

    
   
   

    

[TejasMahind123]

@mabdelrahman76 ,  did you find any solution on this one ?

[v-eqin-msft · Accepted solution]

Hi @mabdelrahman76 ,

If you dont want to have constant value for Status , just remove the filter in measure, like:

Measure = 
var _max=CALCULATE(MAX('Table'[Modified]),FILTER(ALLSELECTED('Table'),[CD_Trading_Id]=MAX('Table'[CD_Trading_Id])))
return IF(MAX('Table'[Modified])=_max,1,0)

It will work, please try.

Best Regards,
Eyelyn Qin

[v-eqin-msft]

Hi @mabdelrahman76 ,

You could refer to this blog about How to provide sample data in the Power BI Forum

I have built some dummy data to test, please try to create a flag measure and then apply it to filter pane.

Measure = 
var _max=CALCULATE(MAX('Table'[Modified]),FILTER(ALLSELECTED('Table'),[CD_Trading_Id]=MAX('Table'[CD_Trading_Id]) && [Status]="Closed Cases"))
return IF(MAX('Table'[Modified])=_max,1,0)

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mabdelrahman76]

Hi @v-eqin-msft , thanks for the feedback, but i dont want to have constant value for Status column, also i would likw to achive to return one value per group column "CD_Trading_id" based on the last modified date of the date range selection. i modified the data as what is shows in the below screeshoot to have good example.

my goal is to be able to return the maxiumum the values based on the max modified date range for each group in the date range selection, in my example if no selection i will return the below values based on the max date for each group.

[v-eqin-msft]

Hi @mabdelrahman76 ,

According to your description, it seems that you just need to apply filters , like "Status="Closed Cases".

Can you please share your pbix file after removing sensitive data or some dummy data to help us clarify your scenario?

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mabdelrahman76]

@v-eqin-msft , thanks for the responce, how can i share the BI repoort here.

what i would like to achive in simple words, that i have slicer with date batwwen, and i have group and each group has values overtime, i would like to see the most receent values and sum based on the date range selection.

Please let me know how can i share the BI report?

Thanks

Mohamed

[littlemojopuppy]

There are several simple ways to calculate the last date in a range of values.  You could use the MAX function, LASTDATE function, or the LASTNONBLANK function.  Hope this helps!

[mabdelrahman76]

@littlemojopuppy thanks for prompt reply, i know but i am still need to filter out non corresponding values from the matrix as what i explained in my screenshoot and the total value should be "1", therefore i am trying to find a solution to display the value for the max date in each group in my i.e. i should return "closed cases" in group "31", i might need to use differnt solution to create summerize table but the table's values need to be calculated dynamically based on date range selection from the slicer, please review my previous reply and advise?

[mabdelrahman76]

Also, i am trying differnt another solution is to create summerize table based on the date range selection column "Modified" in the original table "Test" but unfortunatly when i modifiy the range date in the slicer still pickup the max date from the original table, is there anyway to get the new date range inside the summerize table?

MARIZE Tabe =

SUMMARIZE(Test,Test[CD_Trading_Id],

"Modified",calculate(VALUES(Test[Modified]), filter(ALLSELECTED( Test[ID]), Test[id] = max(Test[id]))),

"Noofcase", CALCULATE(VALUES(Test[No. of Cases]),filter(Test,Test[Id]=MAX(Test[Id]))),

"Status",calculate(VALUES( test[Status]), filter(ALLSELECTED( Test[ID]), Test[id] = max(Test[id]))))