Forum Discussion

kirkademidov's avatar
kirkademidov
Frequent Visitor
8 years ago
Solved

To show data by max date range value on slicer

Good day! 

 

I have this kind of data

 

 

 

And I have this kind on Report 

 

 

 

I need to show on Current Users Card  values from table - relatively to max date chosen on date slicer. Values on the table are snapshots so I don't need to summarize it only to show. 

 

I suppose that I need to create measure by FILTER data but I`m a little bit confused which dax formula to use. 

 

Thanks a lot!

 
 
Need Help
  • McCow's avatar
    McCow
    8 years ago

    Hi kirkademidov,

    with your PBIX it's OK.

    You need just one more measure, like this:

    Last_Quantity = AVERAGEX(
	CALCULATETABLE(
		TOPN(1;'stg StgFactUsers';'stg StgFactUsers'[PkDateKey];DESC)
	);'stg StgFactUsers'[Quantity]
)//AVG

    And I changed a little your PBIX, so you can look on all my changes HERE:

     

    Best regs

     

     

6 Replies

  • McCow's avatar
    McCow
    Icon for Resolver III rankResolver III
    8 years ago

    Hi kirkademidov

     

    try this measure, to calculate max date value:

    Max_Date_Value = 
MAXX(
	KEEPFILTERS(VALUES('Dates'[PkDateKey]));
	CALCULATE(MAX('Dates'[PkDateKey]))
)

    I suggest, htat you use "PkDateKey" for your slicer. For better result you can send  a PBIX file with example data. It will be IMHO easier.

     

    Best regs

     

    • kirkademidov's avatar
      kirkademidov
      Frequent Visitor
      8 years ago

      McCow and  v-jiascu-msft, thanks for you answer! 

      I didnt find the way to attache file - you can find it here Dummy pbix  - I`ve wrote explanation inside 

      • McCow's avatar
        McCow
        Icon for Resolver III rankResolver III
        8 years ago

        Hi kirkademidov,

        with your PBIX it's OK.

        You need just one more measure, like this:

        Last_Quantity = AVERAGEX(
	CALCULATETABLE(
		TOPN(1;'stg StgFactUsers';'stg StgFactUsers'[PkDateKey];DESC)
	);'stg StgFactUsers'[Quantity]
)//AVG

        And I changed a little your PBIX, so you can look on all my changes HERE:

         

        Best regs

         

         

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    Hi kirkademidov,

     

    Can you share a dummy sample please? I can't see any connections between the data and the visualizations. You measure could be like this:

    No UID =
CALCULATE (
    MAX ( 'table'[uid] ),
    FILTER ( ALL ( 'datetable' ), 'datetable'[date] = MAX ( 'datetable'[date] ) )
)

    Best Regards,

    Dale