DAX query to display prior month result

[nishamartis]

Hello,

I want to get the Previous month Sales Count. I need to use only DAX queries to achieve this. I used the following DAX queries. It didn't give me the desired result. Can anyone help me with this?  

PriorMonthCount = CALCULATE(COUNT(FactTable[Column]),PREVIOUSMONTH(DimTable[Date]))

PriorMonthCount = CALCULATE(COUNTA(FactTable[Column]),PARALLELPERIOD(DimTable[Date],-1,MONTH))

Thanks in advance.

[v-huizhn-msft]

Hi @nishamartis,

Please create relationship between your FactTable and DimTable, I try to reproduce your scenario and get the expected result.

I create my sample data table and calendar table.

Calendar = CALENDAR(DATE(2017,1,1),DATE(2017,3,5))

On the Home tab, please click Manage Relationships->New. In the Create Relationship dialog, in the first table drop-down list, select a table, and then select the column you want to use in the relationship.

Then create measure using similar formulas like yours.

previous = CALCULATE(SUM(Table3[value]),PREVIOUSMONTH(Table3[Date]))

previous-month = CALCULATE(SUM(Table3[value]),PARALLELPERIOD('Calendar'[Date],-1,MONTH))

Finaly create a table used to display the rsult.

If you have any other problem, please feel free to ask.

Best Regards,
Angelia

[nishamartis]

Hi @v-huizhn-msft

I tried the method you explained.

I am getting the sum(sales) for all the months except present month i.e. current month is February , I am getting results from July 2016(I started collecting data from this day) to January 2017. What I want is data for January 2017 only. If current month is January 2017 then I need data for Decemeber 2016. Hope my query is clear. Thanks for helping out.

[v-huizhn-msft]

Hi @nishamartis,

It's werid, do you mind share your .pbix file to find deep cause? Thanks a lot.

Best Regards,
Angelia

[nishamartis · Accepted solution]

Hi @v-huizhn-msft

I cant share the file since it is project related and confidential.

Thanks a lot for your help.

I got a solution for Prior month result and is as follows:

PriorMonthCount = CALCULATE(COUNT(FactTable[Column),PREVIOUSMONTH(DATESMTD(DimDateTable[Date])))

[v-huizhn-msft]

Hi @nishamartis,

Thanks your detail statement, do you resolve your issue? If it does, please mark corresponding reply as answer for help more people. Thanks a lot.

Best Regards,
Angelia

[Anonymous]

Can anybody answer this? I have the same query.