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
sanjayshah
Advocate I
Advocate I

Show Sales of Previous Months

I have a seemingly simple requirement, but am just not able to find a solution for the same.

I have a Fact table for Sales (Table1)  which has sales data for various dates in the past year. I have a dimension table for dates (sys_dim_date_dimension_full) . I have connected the 'Tran Date' to the 'FullDate' in the dimension table. If I create a bar chart of monthwise sales, it works beautifully.

Now my users want to select a Month say October 2017, and they want to see the sales monthwise for a period prior to 1st October 2017. They do not want to see the total sales prior to 1st October 2017, but want to see it month wise i.e. Sales for Jan 2017, Feb 2017 and so on till Sept 2017. It seems very simple, we are just asking PowerBI to ignore all sales from 1st October 2017 onwards, and plot the rest of it month wise. But i am not able to get the correct dax formula for the same. I used the following :

 

NewSum = calculate(sum(Table1[Sale]), filter(all(sys_dim_date_dimension_Full[FullDate]), sys_dim_date_dimension_Full[FullDate]<max(sys_dim_date_dimension_Full[FullDate])))

 

The above shows all the sales of prior month correctly, but it shows all in the selected month i.e. October 2017.

 

This is very urgent, and  i would be grateful for any help.

 

Sanjay Shah

 

 

Sanjay Shah

Exn 221.

 

9 REPLIES 9
Abduvali
Skilled Sharer
Skilled Sharer

Hi @sanjayshah,

 

 

Try this:

NewSum = calculate(sum(Table1[Sale]), filter(all(sys_dim_date_dimension_Full), sys_dim_date_dimension_Full[FullDate].Month]<max(sys_dim_date_dimension_Full[FullDate].[month])))

 

 

Regards

Abduvali

Btw the .[month] suffix which you added in max(sys_dim_date_dimension_Full[FullDate].[month]) ..what does it do ? It did not give any however.

@sanjayshah,

 

When you say max[date] it will take the newest date like 24/11/2017

But if you say max[date].[month] it will take November

Ok. Will store the file in a jiffy.

Thanks.

 

Sanjay

i have put your code in the measure newsum1

What i want to see is on Page 2. i have achieved this by creating a disconnected slicer. But my users dont want a disconnected slicer. they want the previous months sales on the regular date slicer.

 

Thanks.

 

Sanjay

Thanks, but this does not work.

is there any way in which i can send the pbix file on this forum or by any other method so that you can try it out ?

@sanjayshah,

 

 

Store it on OneDrive or Google drive and share the link

 

 

 

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.