Forum Discussion

artlemaks's avatar
artlemaks
Icon for Helper II rankHelper II
6 years ago

Report to compare Current Values vs Previous Values based on ID

Hi.

 

I am currently working on a report that I want to add to our dashboard.

 

The report that we are looking for is to compare current value vs previous value by using IDs from the database.

This is what the table looks like


So the ID for current values would be 8027 and for previous values 7995 in the given example. I know this can be achieved with DAX using filtered values, but I haven't been able to find a way to dynamically set the value for the ID for previous values

 

The DAX calculation that I have used is 

 

 

Stock at Cost for 7995 2 = 
CALCULATE(
	SUM('godigitally_easycoun_st view_stocktake_details'[Stock at Cost]),
	'godigitally_easycoun_st view_stocktake_details'[stocktake_id]
		IN { 7995 }
)

 

The value of 7995 is what needs to change. 

 

Is DAX calculation the right way to go, or what is the most efficient way of achieving the results that I'm expecting? 

Need Help
Show and Tell

5 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    artlemaks , try new columns

    last date = maxx(filter(Table, [store_id]= earlier([store_id]) && [stock date] < earlier([stock date])),[stock date])
last value  = maxx(filter(Table, [store_id]= earlier([store_id]) && [stock] = earlier([last Date])),[ID])
    • artlemaks's avatar
      artlemaks
      Icon for Helper II rankHelper II
      6 years ago

      Hi amitchandak .

       

      I have tried to create the column as suggested by you using

       

      last date = maxx(filter('godigitally_easycoun_st stocktake_master', [store_id]= earlier([store_id]) && [Stocktake Date] < earlier([Stocktake Date])),[Stocktake Date])

       

       But I am getting errors on earlier([store_id]) and earlier([Stocktake Date]). It says

       

      Parameter is not the correct type. Cannot find name '[store_id]'.

       

       And same error for Stocktake Date. 

       

      Any suggestions?

  • Tahreem24's avatar
    Tahreem24
    Icon for Super User rankSuper User
    6 years ago

    artlemaks ,

    Try the below Column:

    Previous Record = CALCULATE(SUM(Table[Cost]),FILTER(Table,Table[DateCOlumn]<EARLIER(Table[DateColumn])))

     

    • artlemaks's avatar
      artlemaks
      Icon for Helper II rankHelper II
      6 years ago

      Tahreem24 same problem as above, it is not allowing me to select the Stocktake Date Column as an option for EARLIER() expression. 

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi artlemaks ,

         

        This works fine on my side.

        Column = CALCULATE(MAX('Table'[id]),FILTER('Table','Table'[stock_date]<EARLIER('Table'[stock_date])))

         

        Best Regards,

        Jay

         

        Community Support Team _ Jay Wang

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