topic Re: Show sales until last available date in DAX Commands and Tips

Show sales until last available date

RoshanK — Sat, 01 Apr 2023 09:53:11 GMT

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?

Re: Show sales until last available date

MohammadLoran25 — Sat, 01 Apr 2023 10:22:35 GMT

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.

Re: Show sales until last available date

RoshanK — Sat, 01 Apr 2023 10:29:25 GMT

Hi,

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

Re: Show sales until last available date

MohammadLoran25 — Sat, 01 Apr 2023 10:33:38 GMT

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

What is the role of your date slicer then?

Re: Show sales until last available date

RoshanK — Sat, 01 Apr 2023 10:47:23 GMT

Yes !

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

Re: Show sales until last available date

MohammadLoran25 — Sat, 01 Apr 2023 10:56:45 GMT

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

Re: Show sales until last available date

MohammadLoran25 — Sun, 02 Apr 2023 07:27:48 GMT

RoshanK It was what you needed?

Re: Show sales until last available date

RoshanK — Sun, 02 Apr 2023 10:09:48 GMT

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!

Re: Show sales until last available date

Anonymous — Fri, 14 Apr 2023 07:53:59 GMT

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

Re: Show sales until last available date

RoshanK — Fri, 14 Apr 2023 12:16:40 GMT

Hi,

This solves the problem. Thank you!