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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
mosman
Frequent Visitor

DAX Previous Month and Filtering

Hello all,

 

I have table with sales and dates, the table is an aggregate table, it calculate the total sales for the month end.

it looks like this:

Product_Category   Sales Amount   Date

Bikes                       29324230.99     20190131

Bikes                       423410540.87   20190228

Jackets                   480234.12          20190131

and so on.

I would like to create a measure for sales for previous month. so, I create date table and relate it to my sales table. I was able to get the correct sales amount and previous month sales amount, but the figure wouldn't be correct unless I use the date from calendar table, but that table has so many records and users have to scroll for a long time to select the correct date. when I use the date from the main table, the calcuation for the prior month wasn't correct. How can solve this issue?

1 ACCEPTED SOLUTION
Nathaniel_C
Super User
Super User

Hi @mosman 

Could you create a column that gets the max date for each month in your date table? Then create a helper table that is linked to your date table, with only the distinct values of that column?

 


Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

7 REPLIES 7
Nathaniel_C
Super User
Super User

Hi @mosman 

Could you create a column that gets the max date for each month in your date table? Then create a helper table that is linked to your date table, with only the distinct values of that column?

 


Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Nathaniel_C
Super User
Super User

Hi @mosman ,

Using the column from your date table, format it like this and make the title as shown.


Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

 

 

 

 

slider.PNG

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Hi Nathaniel_C,

 

the users would like to see one date at the time like this:

Capture.PNG

Hi @mosman ,

Is it possible to share the measure that does not work with us? 

Thanks,

Nathaniel





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




the measure looks like this:

Starting Balance Amount = CALCULATE(SUM('FactSales'[currentBalance]),ALL(DimDate),PARALLELPERIOD(DimDate[Date],-1,MONTH))
mosman
Frequent Visitor

when I use the date from the time intelligence table in the slicer, it looks like this:
Capture2.PNG

but I want it to look like this:
Capture.PNG

Hi @mosman ,

Might you create a column in the Date table that gives you the max date for each month and use that for your slicer?
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.