Last date prev. month and other calculations

[Anonymous]

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

[JorgeMarroGT]

Hi there!  This is Jorge from Guatemala, Central America...

I want to let you know that this code was key to implement a solution in Power Query:  

code: Date.EndOfMonth(Date.AddMonths(DateTime.Date(DateTime.LocalNow()),-1))

It retrieves the last day of a previous month based on today's date, for example, Today = 02/10/2023 (Feb 10th. 2023) so that code brings 01/31/2023 (Jan 31st. 2023) ~ the last day of the previous month. 

The colleague who solved and shared is a genius!

Thanks a lot.

Jorge

[CheenuSing · Accepted solution]

@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.

[mikeborg82]

 @CheenuSing  should these be measures or columns?

[CheenuSing]

Hi @mikeborg82

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

Cheers

CheenuSing