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!

View solution in original post

 @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
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors