Show Sales of Previous Months

[sanjayshah]

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.

[Abduvali]

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

[sanjayshah]

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.

[Abduvali]

@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

[sanjayshah]

Ok. Will store the file in a jiffy.

Thanks.

Sanjay

[sanjayshah]

PBIX File

[sanjayshah]

i have put your code in the measure newsum1

[sanjayshah]

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

[sanjayshah]

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 ?

[Abduvali]

@sanjayshah,

Store it on OneDrive or Google drive and share the link