cancel
Showing results for
Did you mean:
Anonymous
Not applicable

Last date prev. month and other calculations

Hello community,

I have following requirements with a fact tables which contains sales data:

1. I want to calculate the sum of sales in the year 2016

2. I want to calculate the sum of sales of the actual month

3. I want to calculate the sum of sales of the previous month (looking from today!!)

4. I want to calculate the sum of sales from the 1. of the year to day befor the last day of the previous month

My solution:

1. Sales Amount 2016 = CALCULATE(SUM(Sales[Amount]); 'Calendar'[YearKey] = 2016)

=> Okay, that is not a problem, it works

2. Sales Amount Actual Month = CALCULATE(SUM(Sales[Amount]);
MONTH(TODAY()) = MONTH(Sales[Date]) && YEAR(TODAY()) = YEAR(Sales[Date]))

=> Okay, works too

Now it gets diffcult:

3. I need the last day from the last month. I dont find any function to get this in dax. So I added a column to my date table with PowerQuery by following M code: Date.EndOfMonth(Date.AddMonths(DateTime.Date(DateTime.LocalNow()),-1))

Next I created a Measure in DAX:

Sales Amount Previous Month = CALCULATE(SUM(Sales[Amount]);
FILTER(ALL('Calendar');
YEAR('Calendar'[LastDayPreviousMonth]) = 'Calendar'[YearKey]
&& MONTH('Calendar'[LastDayPreviousMonth]) = 'Calendar'[MonthOfYear]
))

=> It works fine, but is there any solution in dax?

4. Same as by 3., but I added second column to my date table with M:

And the DAX Measure:

Sales Amount YT Previous Month = CALCULATE(SUM(Sales[Amount]);
DATESBETWEEN('Calendar'[DateKey]; DATE(YEAR(TODAY()); 01; 01); LASTDATE('Calendar'[LastDayPrePreviousMonth])))

=> It also works fine ....

My question is, is there any better solution to get the results? I preferred DAX.

The function in DAX DATEADD or PREVIOUSMONTH I can't use, because I need the previous month from TODAY, and not the last date in the fact table. In my visualisation I only use a Matrix where I want to display only the measure with a product category. I don't display the date table with any fields.

Bye,

Philipp

1 ACCEPTED SOLUTION
Community Champion

@Anonymous

1. I am assuming you have a date table called Calendar and have a column called FullDate of the format Dd/MM/YYYY.

2. I am assuming your Year begins from January.

3. To get to the solution as a measure instead of column do the following.

4. Create a  Measure called LastDateCurrentYear = TODAY(). This will give you 26/06/2016.

5. Create a Measure called EndofCurrentMonth = EOMONTH([LastDateCurrentYear ]). This will give you 30/06/2016

6. Create a Measure called EndofPreviousMonth = EOMONTH([EndofCurrentMonth],-1). This will give you 31/05/2016

7. Create a Measure called FirstDayCYR = Date(year(TODAY()),1,1). This will give you 01/01/2016.

8. Create a Measure called

YTDSalesUptoPreviousMonth= Calculate([Sales],Datesbetween(Calendar[FullDate],[FIrstDayCYR],[EndofPreviousMonth ])).

This will give you the result of sales year to date upto previous month.

[Sales] is a measure already defined as Sum(SalesTable[SalesAmount]).

In case you find the above steps working please accept it as a solution and also give kudos.

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
3 REPLIES 3
Community Champion

@Anonymous

1. I am assuming you have a date table called Calendar and have a column called FullDate of the format Dd/MM/YYYY.

2. I am assuming your Year begins from January.

3. To get to the solution as a measure instead of column do the following.

4. Create a  Measure called LastDateCurrentYear = TODAY(). This will give you 26/06/2016.

5. Create a Measure called EndofCurrentMonth = EOMONTH([LastDateCurrentYear ]). This will give you 30/06/2016

6. Create a Measure called EndofPreviousMonth = EOMONTH([EndofCurrentMonth],-1). This will give you 31/05/2016

7. Create a Measure called FirstDayCYR = Date(year(TODAY()),1,1). This will give you 01/01/2016.

8. Create a Measure called

YTDSalesUptoPreviousMonth= Calculate([Sales],Datesbetween(Calendar[FullDate],[FIrstDayCYR],[EndofPreviousMonth ])).

This will give you the result of sales year to date upto previous month.

[Sales] is a measure already defined as Sum(SalesTable[SalesAmount]).

In case you find the above steps working please accept it as a solution and also give kudos.

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
Helper II

@CheenuSing  should these be measures or columns?

Community Champion

All as measures. Please go through the solution steps carefully.

Cheers

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

Announcements

CollabDays Lisbon - 26 November 2022

Sessions include practical, hands-on experience that will help you take the next step in your career and know-how.

Difinity Conference 2022

Difinity is the largest Microsoft Data, AI, Power BI, Power Platform and Business Applications Conference in New Zealand.

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Top Solution Authors
Top Kudoed Authors