cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

Last full month sales totals

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Regular Visitor

Re: Last full month sales totals

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 🙂

View solution in original post

3 REPLIES 3
Highlighted
Microsoft
Microsoft

Re: Last full month sales totals

Hi @hwright,

From your description, it could work on my side:

1.PNG

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

 

 

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Regular Visitor

Re: Last full month sales totals

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:

SequentialData help.PNG

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...

SequentialData2 help.PNG

 

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

 

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

 

Thanks!!!

Heather

 

 

Highlighted
Regular Visitor

Re: Last full month sales totals

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 🙂

View solution in original post

Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors