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:

  1. date_for_visual : Date will be used in visual (slicer etc.)
  2. actual_date: actual date's data
  3. sales_demand: monthly sales goal
  4. fulfilled_sales: actual month's sales
  5. fulfilled_order: percentage from (fulfilled_sales/sales_demand)

I've had a problem and it makes me stuck, i just want to show data from previous month whenever the month is changed (when the current month and date is February 1st, i want to show data from yesterday/last day of the month), is there any way to show it?

 

P.S. : I used date_for_visual for the slicer 

 

 

The sample data

 

 

Table realationship

 

 

 

Using actual_date

 

Current condition

  • 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
    )

     

1 Reply

  • 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
    )