Forum Discussion

jdw_msft's avatar
jdw_msft
Frequent Visitor
2 years ago
Solved

Getting data from previous day/yesterday's date

  Hello everyone, i have sample table like this picture: I'll describe the table columns: date_for_visual : Date will be used in visual (slicer etc.) actual_date: actual date's data sales_deman...
  • Jihwan_Kim's avatar
    Jihwan_Kim
    2 years ago

    Hi,

    I am not sure if I understood your question correctly, but I tried to create a sample pbix file like below.

    In the below sample, I tried to create last non blank value for fulfilled sales.

     

     

     

     

    Last Non Blank fulfilled sales Value: = 
VAR _SelectedDate =
    MAX ( 'calendar'[Date] )
VAR _lastnonblankdate =
    MAXX (
        FILTER (
            ADDCOLUMNS (
                FILTER (
                    ALL('calendar'[Date]),
                    'calendar'[Date] <= _SelectedDate
                ),
                "@metrics", CALCULATE ( SUM ( sales[fulfilled_sales] ) )
            ),
            NOT [@metrics] == BLANK ()
        ),
        'calendar'[Date]
    )
RETURN
    CALCULATE (
        SUM ( sales[fulfilled_sales] ),
        REMOVEFILTERS ( 'calendar' ),
        'calendar'[Date] = _lastnonblankdate
    )

     

Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper User
2 years ago

Hi,

I am not sure if I understood your question correctly, but I tried to create a sample pbix file like below.

In the below sample, I tried to create last non blank value for fulfilled sales.

 

 

 

 

Last Non Blank fulfilled sales Value: = 
VAR _SelectedDate =
    MAX ( 'calendar'[Date] )
VAR _lastnonblankdate =
    MAXX (
        FILTER (
            ADDCOLUMNS (
                FILTER (
                    ALL('calendar'[Date]),
                    'calendar'[Date] <= _SelectedDate
                ),
                "@metrics", CALCULATE ( SUM ( sales[fulfilled_sales] ) )
            ),
            NOT [@metrics] == BLANK ()
        ),
        'calendar'[Date]
    )
RETURN
    CALCULATE (
        SUM ( sales[fulfilled_sales] ),
        REMOVEFILTERS ( 'calendar' ),
        'calendar'[Date] = _lastnonblankdate
    )