Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more
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.
Solved! Go to Solution.
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])))
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
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.
Hi @nishamartis,
It's werid, do you mind share your .pbix file to find deep cause? Thanks a lot.
Best Regards,
Angelia
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])))
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
Can anybody answer this? I have the same query.
User | Count |
---|---|
97 | |
67 | |
57 | |
47 | |
46 |