Sales YTD and previous years by complete months

[FatherTheWizard]

Hi,

This is a very common problem but I cannot find solution from this forum for this particular way.

I would need a Sales YTD from this year and previous years. The YTD should be calculated in a way that when MONTH(TODAY()) is September my reporting/complete month would be August. For this I have created already working fields for "Reporting month" and "Reporting year" which are the ones I would like to use. I have also a separete calendar table.

What I want to accomplish is to get a formula which would be the following in Excel language (I am quite new to DAX but familiar with Excel):

=SUMIF(Month_range; "<="&Reporting month; Sum_range)

My goal is to have f.ex. bar charts for 2014, 2015, 2016 and 2017 with "Reporting YTD" figures from each. I would also use this YTD Sales value field for other purposes.

Thanks!

[AkSaidhana]

Hey,

I have simlar scenario,  Need YTD Results for a cost type other than current month and remaining should show as zero.

Data structure look like this.  I need to see YTD Month total till March after that it should show zero.

[Greg_Deckler]

I am thinking TOTALYTD maybe: https://msdn.microsoft.com/en-us/library/ee634400.aspx

But, the general replacement for SUMIF from Excel is using CALCULATE

[FatherTheWizard]

Hi smoupre,

TOTALYTD will not work if I have my calendar filled to 2018? Moreover, I will not want to use September yet since the month is not finished. Instead I would like to use Jan-Aug data from each of the years. Thanks for the help.

[FatherTheWizard]

Hi,

Sorry for spamming but additional question: why is not this working BR DAX Newbie:

Sales YTD = CALCULATE(SUM('Sales'[Sales]);MONTH(DimDateClose[Close Date])<=[Reporting Month])

Where reporting month equals 8 (=current month - 1).

[Greg_Deckler]

Tough to say without sample data and more information, like what exactly isn't working? Perhaps there is a relationship issue? I did this:

Created DimDate table using the following formula:

DimDate = CALENDAR(DATE(2015,1,1),DATE(2017,12,30))

Created sales table using the following formula and an additional custom column:

Sales = CALENDAR(DATE(2015,1,1),DATE(2017,12,30))

Sales = 1000

The second Sales is a column inside the Sales table.

Created the relationship on my Date column.

Created this measure and it appears to work:

MySales = VAR ReportingMonth = 8
VAR ReportingYear = 2017
RETURN CALCULATE(SUM(Sales[Sales]),MONTH(DimDate[Date])<=ReportingMonth && YEAR(DimDate[Date]) = ReportingYear)

MySales = VAR ReportingMonth = 8
VAR ReportingYear = 2017
RETURN CALCULATE(SUM(Sales[Sales]),MONTH(DimDate[Date])<=ReportingMonth && YEAR(DimDate[Date]) = ReportingYear)

[FatherTheWizard]

Thanks for your help despite of the lack of additional information. With that formula I can get 2017 correctly. But I would need the following, any tips?

[v-ljerr-msft]

Hi @FatherTheWizard,

Based on my test, the formula below should work in your scenario. 

1. Add Year, Month column to your DimDateClose table, if there aren't yet.

Year = YEAR(DimDateClose[Close Date])

Month = MONTH(DimDateClose[Close Date])

2. Then use the formula to create a measure, and show it with Year, Month column on the report.

Sales YTD =
VAR reportYTDMonth =
    MONTH ( TODAY () ) - 1
RETURN
    IF (
        MAX ( 'DimDateClose'[MonthNo] ) <= reportYTDMonth,
        CALCULATE (
            SUM ( 'Sales'[Sales] ),
            FILTER (
                ALL ( 'DimDateClose' ),
                'DimDateClose'[Year] = MAX ( 'DimDateClose'[Year] )
                    && 'DimDateClose'[MonthNo] <= MAX ( 'DimDateClose'[MonthNo] )
            )
        )
    )

Regards

[Greg_Deckler]

Or, maybe in addition, create a Month column in Sales like:

Month = MONTH([Date])

And then a running total like:

Sales running total in Month = 
CALCULATE(
	SUM('Sales'[Sales]),
	FILTER(
		ALLSELECTED('Sales'[Month]),
        	ISONORAFTER('Sales'[Month], MAX('Sales'[Month]), DESC)
	),
        Sales[Month]<=MONTH(TODAY()-1)
)

[FatherTheWizard]

EDIT: The Sales (YTD CUML) is currently working correctly. Huge thanks for that! (Still need to figure out the logic to be able to do it on my own in the future)

 Still need help with columns Sales YTD, Sales LY and Sales LY CUML.

Current formulas:

Sales YTD = CALCULATE(
SUM('Sales'[Sales €]);FILTER(DimDate;
MONTH(DimDate[Date])<=MONTH(TODAY())-1);KEEPFILTERS('Sales - Opportunity'[Close Year]))

Sales (YTD CUML) = this is working currently

Current view which I get in PBI:

-Sales for 2015: 1000 per month, 2016: 2000 per month, 2017: 3000 per month.

What I would like to have:

-Sales (YTD CUML) is currently working as it should. Thanks!

[v-ljerr-msft · Accepted solution]

Hi @FatherTheWizard,

The formulas below are for your reference. 

Sales YTD =
VAR reportYTDMonth =
    MONTH ( TODAY () ) - 1
RETURN
    IF ( MAX ( 'DimDateClose'[MonthNo] ) <= reportYTDMonth, SUM ( 'Sales'[Sales] ) )

Sales LY =
VAR reportYTDMonth =
    MONTH ( TODAY () ) - 1
RETURN
    IF (
        MAX ( 'DimDateClose'[MonthNo] ) <= reportYTDMonth,
        CALCULATE (
            SUM ( 'Sales'[Sales] ),
            FILTER (
                ALL ( 'DimDateClose' ),
                'DimDateClose'[Year]
                    = MAX ( 'DimDateClose'[Year] ) - 1
                    && 'DimDateClose'[MonthNo] = MAX ( 'DimDateClose'[MonthNo] )
            )
        )
    )

Sales LY CUML =
VAR reportYTDMonth =
    MONTH ( TODAY () ) - 1
RETURN
    IF (
        MAX ( 'DimDateClose'[MonthNo] ) <= reportYTDMonth,
        CALCULATE (
            SUM ( 'Sales'[Sales] ),
            FILTER (
                ALL ( 'DimDateClose' ),
                'DimDateClose'[Year]
                    = MAX ( 'DimDateClose'[Year] ) - 1
                    && 'DimDateClose'[MonthNo] <= MAX ( 'DimDateClose'[MonthNo] )
            )
        )
    )

Regards

[Hvaldes]

Hello!

I was looking the formula but i don't understand why you compare the max of the column month if always will be 12, so, i think that never the sentence entry in IF condition of sales.

thanks

 MAX ( 'DimDateClose'[MonthNo] ) <= reportYTDMonth,

[FatherTheWizard]

@v-ljerr-msft

Actually one question. I don't understand why the figures show correctly in the table format but when I try to apply these measures in charts nothing appears. Any ideas?

BR Wizard

[v-ljerr-msft]

Hi @FatherTheWizard,

You need to show both Year and Month column as Axis on the Chart, and click (expand all down one level) on left top of the Chart to get the expected result. 

Regards

[FatherTheWizard]

Thank you so much! I finally understood the <= MAX and = MAX concepts behind these calculations.

[Greg_Deckler]

Try creating a Year column in your Sales data like:

Year = YEAR([Date])

And then a measure like this:

MySales = VAR ReportingMonth = 8
RETURN CALCULATE(SUM(Sales[Sales]),FILTER(Sales,MONTH(Sales[Date])<=ReportingMonth),KEEPFILTERS(Sales[Year]))

And create a table with Year and MySales.

[FatherTheWizard]

Hi,

Thank you for your input despite the lack of additional information. Your solution was great for getting the YTD figures for this year. What I would like to have is the following (I could not copypaste the table here):

With these figures I could compare the years.