cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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:

Date.EndOfMonth(Date.AddMonths(DateTime.Date(DateTime.LocalNow()),-2))

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.

 

Thanks in advance!

 

Bye,

Philipp

1 ACCEPTED SOLUTION
CheenuSing
Community Champion
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]).

 

Please do not hesitate to reply if you need further assistance.

 

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!

View solution in original post

3 REPLIES 3
CheenuSing
Community Champion
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]).

 

Please do not hesitate to reply if you need further assistance.

 

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!

 @CheenuSing  should these be measures or columns?

Hi @mikeborg82

 

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!

Helpful resources

Announcements
collabdays lisbon

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

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

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 â__ December 6-8, 2022

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.