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!    