Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
sevens
Regular Visitor

MoM Sales Data by Choosing Month from Visualization

I'm trying to create a sales dashboard that shows our customer's sales compared to previous month sales. The only thing I don't understand how to do is how to script the dynamic month selection into the DAX formula. The month isn't chosen from a slicer, but a clustered column chart right above it. 

 

This is not current data, I'm testing this dashboard with last year's sale data. It only has a full year's worth of data loaded, so January won't have a previous month. Also, if anyone can recommend a better chart to display the figures, I would appreciate it. Right now I'm using a Tornado graph to display the two figures (bottom right.) 

 

The formula that's in my "Previous Month Sales" calculation is = CALCULATE(SUM('2017 sales history'[Extended Price]),PARALLELPERIOD('2017 sales history'[Invoice Date],-1,MONTH)). It's displaying a figure, but I'm not sure what it's calculating and it stays static. No matter what month I select at the top it doesn;t change. It's the darker color on the right. 

 

Dashboard.PNG

2 REPLIES 2
v-piga-msft
Resident Rockstar
Resident Rockstar

Hi @sevens,

 

I 'm not very clear about your requirement, do you want to select one of the month in the a clustered column chart, then the  Tornado graph will be filterd?


 It's displaying a figure, but I'm not sure what it's calculating and it stays static. No matter what month I select at the top it doesn;t change. It's the darker color on the right. 

 

Do you mean the output like this? This is the interaction between the visuals.

 

Capture.PNG

 

Besides, it seems that your measure formula should be right, if the date in PARALLELPERIOD function to be the Date[Datekey] will be better.

 

What is your desired output? Could you describe in more details?

 

If it is convenient, could you share a dummy pbix file which can reproduce the scenario, so that we can help further investigate on it? You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.)

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thank you for the reply, Cherry! Here's what I'm looking for (and there may be a better way to go about it. I'm totally up for suggestions.)

 

When the pbix file loads, the clustered column will automatically filter to the current month. That, in turn, will automatically make the tornado chart filter to current month AND previous month sales. The darker color in my image represents the previous months sales. But like I said, the figure will not change no matter what month I select in the clustered column chart. It just stays the same. That measure is a custom calculation with the PARALLELPERIOD forumula I have in my original comment. 

 

I think there's something missing from the formula. I'm having a hard time figuring out how I can pass the dynamic time period to it (the information that the user selects from the clustered column chart.) It's like it's not reading what month is being selected. 

 

Hopefully this helps make more sense! Thanks again. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.