Show sales until last available date

[RoshanK]

I have simple calendar and sales table.My sales table has data until December 2022.

What I am looking for is a measure that when user selects January/ Feb/March 2023, it shows the data until latest available time which is Dec 2022.

Criteria: User can select multiple years but only single month.

For example; In this picture Jan 2023 is selected, since the Jan 2023 data is not available, I want all my Year-Month to change to Dec, so 2022-01 becomes 2022-12 and the rest follows. Any idea how this could be accomplished?

[v-yiruan-msft · Accepted solution]

Hi @RoshanK ,

If I understand correctly, you want to get the data in selected date period. And if there is no data in the selected year and month, it need to display the data which is the last available date before the selected date period. For example, if you select the year 2023 and the month Feb, but there is no data for 2023-02 in the fact table. And the last available date is on 2023-01, so the expected data should be the value on 2023-01. Am I right? If yes, you need to follow the steps below to get it:

1. Make sure the field [Year-Month] and slicer field [Year]&[Month] are not from the same table, and NOT create relationship between the tables. If no, please create a new Year-Month dimension table first just as below screenshot

2. Create a measure as below to replace the original one

Measurea =
VAR _selyears =
    ALLSELECTED ( 'Date'[Year] )
VAR _selmonth =
    SELECTEDVALUE ( 'Date'[MonthNum] )
VAR _selym =
    SELECTEDVALUE ( 'YM'[Year-Month] )
VAR _ymyear =
    VALUE ( LEFT ( _selym, 4 ) )
VAR _ymmonth =
    VALUE ( RIGHT ( _selym, 2 ) )
VAR _sales =
    SUMX (
        FILTER (
            'sales',
            YEAR ( 'sales'[xxdate] ) = _ymyear
                && MONTH ( 'sales'[xxdate] ) = _selmonth
        ),
        [Total Sales]
    )
VAR _maxdate =
    CALCULATE (
        MAX ( 'sales'[xxdate] ),
        FILTER (
            'sales',
            'sales'[xxdate] < EOMONTH ( DATE ( _ymyear, _selmonth, 1 ), 0 )
                && NOT ( ISBLANK ( [Total Sales] ) )
        )
    )
VAR _pymsales =
    SUMX (
        FILTER (
            'sales',
            YEAR ( 'sales'[xxdate] ) = YEAR ( _maxdate )
                && MONTH ( 'sales'[xxdate] ) = MONTH ( _maxdate )
        ),
        [Total Sales]
    )
RETURN
    IF (
        (
            _ymyear
                IN _selyears
                    && _ymmonth = _selmonth
                    && NOT ( ISBLANK ( _sales ) )
        ),
        _sales,
        IF (
            _ymyear = YEAR ( _maxdate )
                && _ymmonth = MONTH ( _maxdate ),
            _pymsales,
            BLANK ()
        )
    )

If the above one can't help you get the expected result, could you please provide some raw data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples? It would be helpful to find out the solution. You can refer the following links to share the required info:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[RoshanK]

Hi @MohammadLoran25 ,

Thanks for your reply. I appreciate it!

However, this was not exactly how I wanted. 
For example, 

If user selects Feb 2023 & if the data is available until Jan 2023, your measure will return Dec 2022 value instead of jan 2022.

This is a bit confusing because the data are not updated timely. Hence, brings complexity!

[v-yiruan-msft · Accepted solution]

Hi @RoshanK ,

If I understand correctly, you want to get the data in selected date period. And if there is no data in the selected year and month, it need to display the data which is the last available date before the selected date period. For example, if you select the year 2023 and the month Feb, but there is no data for 2023-02 in the fact table. And the last available date is on 2023-01, so the expected data should be the value on 2023-01. Am I right? If yes, you need to follow the steps below to get it:

1. Make sure the field [Year-Month] and slicer field [Year]&[Month] are not from the same table, and NOT create relationship between the tables. If no, please create a new Year-Month dimension table first just as below screenshot

2. Create a measure as below to replace the original one

Measurea =
VAR _selyears =
    ALLSELECTED ( 'Date'[Year] )
VAR _selmonth =
    SELECTEDVALUE ( 'Date'[MonthNum] )
VAR _selym =
    SELECTEDVALUE ( 'YM'[Year-Month] )
VAR _ymyear =
    VALUE ( LEFT ( _selym, 4 ) )
VAR _ymmonth =
    VALUE ( RIGHT ( _selym, 2 ) )
VAR _sales =
    SUMX (
        FILTER (
            'sales',
            YEAR ( 'sales'[xxdate] ) = _ymyear
                && MONTH ( 'sales'[xxdate] ) = _selmonth
        ),
        [Total Sales]
    )
VAR _maxdate =
    CALCULATE (
        MAX ( 'sales'[xxdate] ),
        FILTER (
            'sales',
            'sales'[xxdate] < EOMONTH ( DATE ( _ymyear, _selmonth, 1 ), 0 )
                && NOT ( ISBLANK ( [Total Sales] ) )
        )
    )
VAR _pymsales =
    SUMX (
        FILTER (
            'sales',
            YEAR ( 'sales'[xxdate] ) = YEAR ( _maxdate )
                && MONTH ( 'sales'[xxdate] ) = MONTH ( _maxdate )
        ),
        [Total Sales]
    )
RETURN
    IF (
        (
            _ymyear
                IN _selyears
                    && _ymmonth = _selmonth
                    && NOT ( ISBLANK ( _sales ) )
        ),
        _sales,
        IF (
            _ymyear = YEAR ( _maxdate )
                && _ymmonth = MONTH ( _maxdate ),
            _pymsales,
            BLANK ()
        )
    )

If the above one can't help you get the expected result, could you please provide some raw data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples? It would be helpful to find out the solution. You can refer the following links to share the required info:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[RoshanK · Accepted solution]

Hi,

This solves the problem. Thank you! 

[MohammadLoran25]

Hi @RoshanK ,

Not Clear completely.

So in your image, next to 2018-01 you need to show the value for which date? Should be cumulative? what interval?

Please share your desired result. 

[RoshanK]

Hi,

It sounds a bit weird but I want to show 2018-12 value in 2018-01.

[MohammadLoran25]

So you need to show the value of last month per year?

What is the role of your date slicer then?

[RoshanK]

Yes !

When the data is updated on time, the slicer is used.

[MohammadLoran25]

Follow these steps:

1-In your Date Table, Create a calculated column:

Month = Month(DateTable[Date])

2-Then Create the measure below:

Measure =
CALCULATE (
    SUM ( SalesTable[Value] ),
    FILTER (
        ALL ( DateTable ),
        DateTable[Year] = MAX ( DateTable[Year] )
            && DateTable[Month] = 12
    )
)

If this answer solves your problem, give it a thumbs up and accept it as a solution so the others would find what they need easier.

Regards,

Loran

[MohammadLoran25]

@RoshanK It was what you needed?