Last full month sales totals

[hwright]

Good Evening,

Ok, so I have tried to use the forum...

Goal: I'm trying to create a "card" that reflects the total sales for the last full month. I just want it to update automatically based on the date you view the dashboard (i.e. if today is 10/17, the card will give you September's data; if today is 8/9, it will give you July's data, etc.).  I found the steps below within a forum... but the 'monthsequentialnumber' and 'currentmonthsequentialnumber' columns are not working. I get errors that say the "year" isn't spelled correctly...  Then, there are times that I will get it to work - a column will be created, but each row for that column has "Error" in it... and if you click it... bad things happen 😞

This is what I was trying to use:

1. I am assuming you have a date table with no gaps. 

2. I am also assuming you want to show the chart / report for only previous month at all times. This report / chart will not show for any other months based on any other filter. 

2. Create a Column in the date table  called as MonthSequentialNumber as

     MonthSequentialNumber = year( date in date table ) * 12 + Month( date in date table) - 1

     This will create a unique number for each combination of month and year.

3. Create a measure called CurrentMonthSeqeuntialNumber = Year(Today()) * 12 + Month(today()) - 1

    This will find the MonthSequentialNumber based on todays date. 

4. Create a column in Date table called Show as

      Show = IF ( [MonthSequentialNumber ] = [CurrentMonthSeqeuntialNumber ]  - 1, 1, 0) 

5. Show will  have a value of 0 or 1 in the date table.

I'm trying to add it to the query editor (doing it within the desktop version seems to confuse me. I'm willing to do it either way, so any help is very appreciated. I'm so completely lost, frustrated and ready to give up (or throw my computer out the window...which is frowned upon ;)... ).

Any help would be greatly appreciated.

Heather

[v-danhe-msft]

Hi @hwright,

From your description, it could work on my side：

Could you please offer your sample file and post your desired result if possible? Also, you could download my pbix file to have a view.

Regards,

Daniel He

[hwright]

FINALLY! I was able to get the CurrentMonthSequentialNumber to work; however, the dates in my date table don't want to cooperate...

I'm using the following formula:

MonthSequentialNumber = year(viewLeadSummary[CloseDate]) * 12

I'm just trying to get it to recognize 2018*12 = 24,216, but you see, from the screenshot below, I'm getting a number in the millions.

Here's what I'm getting:

When I try to use the whole formula

MonthSequentialNumber = YEAR('Date'[Date] ) * 12 + Month( 'Date'[Date]) - 1

I'm getting a number in the 600M range for January 2018...

Any thoughts on why it would giving me this crazy date?

I can't share my data due to privacy requirement 😕

Thanks!!!

Heather

[hwright · Accepted solution]

Update... I had to go into the query and add 4 columns:

Year column

Month column

Yearsequence (Yearcolumn * 12)

Monthsequence (Month - 1)

Then I had to save and apply those changes... then went back to my visualization and created the MonthSequentialNumber with the following formula:

MonthSequentialNumber = viewLeadSummary[Yearsequence] + viewLeadSummary[MonthSequence]

From there, I created the conditional column of "Show"...but it wasn't giving me a "1" or a "0"... it gives me a completely different number... so because the last full month recognized September, the "Show" is 26054 (?) instead of a "1".

I ended up creating a page level filter using the Show field and had it look for any field greater than zero, and it worked... not the best way of doing it (having so many columns and so many variables), but it worked...

Thanks for the response 🙂