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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
nishamartis
Frequent Visitor

DAX query to display prior month result

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.

1 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])))

View solution in original post

6 REPLIES 6
v-huizhn-msft
Employee
Employee

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.

1.PNG

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.

2.PNG

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

Best Regards,
Angelia

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.

 

Hi @nishamartis,

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

Best Regards,
Angelia

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])))

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
Not applicable

Can anybody answer this? I have the same query.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.