DAX - Calculating Sales through prior month end

[rsimcoe]

Hello,

I am fairly new to DAX and I am struggling in creating a measure that calculates the sum of sales through the prior month end. I have been able to calculate year to date sales but I only want my data to show through the prior month end. I want to have some visuals populate automatically in powerbi that will only show data year to date through the prior month end. Currently I would have to go in and adjust the filter on the visuals each month to get this same result.

Please let me know if you need an example of my data.

Thanks,

-Robert

[aksl]

I'm trying to use this solution, but I come up with an error every time.  

Here is my language:

ActualsYTD = CALCULATE([Cumulative1], FILTER (ALL(Sheet5[Transaction Date]),'Sheet5'[Transaction Date])< DATE(YEAR(TODAY()),MONTH(NOW()),"01"))

The error I'm getting is as follows:

A function filter has been used in a true/false expression that is used as a table filter expression.  This is not allowed.  

I'm new to DAX, so any guidance is appreciated!

[rsimcoe]

Hello,

I am wondering if it has something to do with your Cumulative1 measure. Could you send over the DAX for that measure?

Thanks,

[aksl]

Thanks for your response and your help~

Cumulative1 = calculate(sum(Sheet5[Total ARR Impact]),filter(All(Sheet5),Sheet5[Transaction Date]<=MAX(Sheet5[Transaction Date])))

[rsimcoe]

I believe that your issue is due to the fact that the Cumulative1 measure has a filter included in it and then you are adding it to another calculate function and filtering it again. I might be wrong but I have a feeling that is the issue.

Does is the Total ARR Impact measure using a CALCULATE as well? I might try using that measure instead of the cumulative1 measure and see if it works for you.

[rsimcoe]

In my example, Total Actuals is just a SUM(DATA(Actuals)) measure to get my total actuals.

[Anonymous]

Hi @rsimcoe, can you post your DAX here?

Let's assume you have a table called Sales with a column called Total which is what you want to sum and a column called DateField. Let's start by creating a base measure, which is just the sum of our Total column:

• TotalMeasure = SUM(Sales[Total])

Then, we'll create a YTD version of this:

• SalesYTD = TOTALYTD(TotalMeasure)

Finally, we'll limit the YTD function so it only calculates for prior months. We can use the TODAY() function to get today's date, and the MONTH() function to get the month number of a date (1-12). So MONTH(TODAY()) would return 1 (since today is 1/18/2017). We then want to evaluate our YTD function for dates where the month is less than today's month, which looks like this:

• SalesYTD (M-1) = CALCULATE(SalesYTD,MONTH(DateField)<MONTH(TODAY()))

Hope that helps! Feel free to post your DAX, or follow up with any questions 🙂

[rsimcoe]

Hello @Anonymous,

Thank you so much for your response. I am still having some trouble with the formulas that you provided. Here is what I have done so far.

The combined table houses my actuals and budget information.

The date table houses dates.

September 30th is our fiscal year end so that was added to the TOTALYTD formula

TotalActuals = SUM('Combined'[Actuals])

ActualsYTD = TOTALYTD([TotalActuals],'Date'[Date],"09/30")

ActualsYTD (M-1) = CALCULATE([ActualsYTD],MONTH('Date'[Date]) < MONTH(TODAY()))

Something appears to be going wrong in the YTD M-1 formula as this measure is pulling in 0 information when I try and use it to populate my visual. I tested the ActualsYTD measure and it seems to be pulling in data correctly. Any additional help is appreciated! 

Thanks again,

Robert

[Anonymous]

Hey @rsimcoe, glad to hear the first 2 measures work!

If your fiscal year starts in October, we'll need to adjust the last calculation accordingly. Our goal is to limit our CALCULATE statement to dates prior to the current month.

The TOTALYTD function will limit our data to the current and prior months. So all we should need to do is remove the current month. Let's modify our date filter to exclude the current month in this way:

ActualsYTD (M-1) = CALCULATE([ActualsYTD],MONTH('Date'[Date]) <> MONTH(TODAY()))

Let me know if this works. If not, can you post your .pbix so I can take a look? 🙂

[rsimcoe · Accepted solution]

Good Morning @Anonymous,

So after further attempts last night I could still not get the final formula to work. What I ended up using was something that looks like this.

The Combined[Post date] is my column in my transactional data that houses the dates the sales were posted.

ActualsYTD = CALCULATE([TotalActuals], FILTER (ALL('Combined'[Post date]), 'Combined'[Post date] < DATE(YEAR(TODAY()),MONTH(NOW()),"01")))

I do not know if this is the ideal way to perform this calculation in DAX but it seems to be working.

Thanks,

[OwenAuger]

@rsimcoe

Just a suggestion in relation to the original post:

It's possible to use any time intelligence function in the context of TODAY() by constructing a single row/column table containing TODAY(), then using that within time intelligence functions.

You would need a separate 'Calendar' table for this to work.

Assuming you have a 'Calendar' table with column 'Calendar'[Date] which is related to your transactional date column 'Combined'[Post date], you can write measures using a pattern like:

ActualsYTD up to last month =
VAR TodayDate =
    TREATAS ( { TODAY () }, 'Calendar'[Date] )
RETURN
    CALCULATE ( [TotalActuals], DATESYTD ( PREVIOUSMONTH ( TodayDate ) ) )

The red part can be modified to maniuplate today's date however you like. This example takes the previous month of dates (relative to TODAY()) and produces YTD dates up to the end of that month.

[v-ljerr-msft]

Hi @rsimcoe,

Brilliant solution! Could you accept your last reply as solution to help others who may have similar issue easily find the answer and close this thread?

Regards

[Hvaldes]

Hello

Thanks for the solution, now how we can get the same results but for the previuos year. I mean, for example if we are now in september and with the last solution we can get the sales YTD (JAN-AUG), how we can create a measure that determine the sales of last year as the same months ( JAN-AUG ).

Thanks