How to show latest value on card

[Anonymous]

I have the following chart, with a card above the chart that shows total sick days. I want that card to show only the latest value and not an aggregate, in this case for August as 301, and then the following month I want the card to change to September value (e.g. 350). However, if someone clicks on a different month, for example August and July, then the card should update to show average of what they selected, or if they select just July, then card should update with July value. 

The way my data model is that I have a table called Sales with columns like Month, Month/Year in format mmmm-yyyy, and Sick Leave. 

[maity_s]

Using card visual, you can show both the values in a single card. In the screengrab shown below, there are 3 values. One above the horizontal line and two below the horizontal line.  For security reasons the content is hidden.

In order to achieve, create your two measures. Various PBI developers here have suggested great options. So use them. After that you can follow the steps below to structure the card visual.

Place the bottom values using the Add Data

The second value can be placed in the conditional format section of subtitle., under the Title section in the format bar. 

Finally make sure the divider option is switched on 

In the conditional format window, select field value, and add the measure in the field - 

You will have to adjust the font size to get the desired visual. You can keep it at 20 px for better visibility.

[v-yadongf-msft]

Hi @Anonymous ,

This is my test table:

Please create two measures:

Latest value = CALCULATE(AVERAGE('Table'[Sick leave]),FILTER('Table',MONTH('Table'[Month/Year]) = MONTH(TODAY()))) 

Average of sick leave = AVERAGE('Table'[Sick leave])

You’d better create two card visuals, one is for latest value, the other is for average of sick leave. One card visual cannot meet the two conditions you mentioned at the same time.

Best regards,

Yadong Fang

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

[Anonymous]

Thank you, however sometimes the data is not received until 2nd week of month and I don't want the card showing blank, for example if october data is not ready, rather than showing blank I want it showing september value until october value has been added. Is it possible?

Also when selecting other columns, is it possible to keep the latest value card as not blank?

[Ashish_Mathur]

Hi,

Share the download link of your PBI file.

[Jihwan_Kim]

Hi,

I tried to create a sample pbix file like below.

I hope the below sample can provide some ideas on how to create a solution for your datamodel.

Sick leave measure: =
VAR _latestmonth =
    EOMONTH ( CALCULATE ( MAX ( Data[Date] ), REMOVEFILTERS () ), 0 )
VAR _sickleave =
    SUM ( Data[Sick leave] )
VAR _sickleaveavg =
    AVERAGEX (
        DISTINCT ( 'Calendar'[Month-Year] ),
        CALCULATE ( SUM ( Data[Sick leave] ) )
    )
RETURN
    IF (
        ISFILTERED ( 'Calendar'[Month-Year] ),
        IF ( HASONEVALUE ( 'Calendar'[Month-Year] ), _sickleave, _sickleaveavg ),
        CALCULATE ( SUM ( Data[Sick leave] ), 'Calendar'[End of Month] = _latestmonth )
    )

[Anonymous]

The date columns and data for sick leave are all in one table

[Tahreem24]

@Anonymous , Create a measure like below:

CALCULATE (SUM ( Table[SickDays] ),Table[Year-Month] = MAX ( Table[Year-Month] ))

Note: Replace Year-Month in above DAX with your own Month or date column