dynamic date and calculating sales from prior month

[mannymann]

Hi,
How can we build a Dax to calculate sales from the current date to the beginning of the month and compare it with a similar date range from the prior month? Also, the date has to be dynamic.
For example, 01-June-2020 and the current date is 25-June-2020.
and we need to compare it with the prior month for the same date range 01-May-2020 and 25-May-2020.
Moreover, if we run it again on the 28-June, the current date should be 28-June and 28-may.

please let me know and thanks for helping 

[v-alq-msft]

Hi, @mannymann 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

You may try the following measures.

CurrentMonthSales = 
var _currentdate = TODAY()
var _startdate = DATE(YEAR(_currentdate),MONTH(_currentdate),1)
return
CALCULATE(
    SUM('Table'[Sales]),
    FILTER(
        ALL('Table'),
        'Table'[Date]>=_startdate&&
        'Table'[Date]<=_currentdate
    )
)

LastMonthSales = 
var _day = DAY(TODAY())
var _startdate = EOMONTH(TODAY(),-2)+1
var _enddate = DATE(YEAR(_startdate),MONTH(_startdate),_day)
return
CALCULATE(
    SUM('Table'[Sales]),
    FILTER(
        ALL('Table'),
        'Table'[Date]>=_startdate&&
        'Table'[Date]<=_enddate
    )
)

Today is 7/1/2020. Here is the result.

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[camargos88]

Hi @mannymann ,

If you have a date calendar, you can use the date intel functions.

Check this link: https://docs.microsoft.com/en-us/dax/sameperiodlastyear-function-dax

[mannymann]

I'm looking for the same period last month comparison. 

So, sameperiodlastyear function doesn't help. 

[camargos88 · Accepted solution]

@mannymann ,

Try this measure:

SamePeriodLastMonth =

VAR _dtStart = MINX(DATEADD(Calen[Date], -1, MONTH), Calen[Date])

VAR _dtEnd = MAXX(DATEADD(Calen[Date], -1, MONTH), Calen[Date])

RETURN CALCULATE(SUM(Orders[Sales]), DATESBETWEEN(Calen[Date], _dtStart, _dtEnd))

[mannymann]

Thank you @camargos88 

I was looking for something where the date is automatic. 

However, It worked. 

[camargos88]

Hi @mannymann ,

I believe it's possible to do it. Give us more details about how you would like to automatize it. For example, get the current month and last and expand them with those day name/times ?

[mannymann]

For example, we want the stats or number for the current and the prior month where the date range as follows:  

Let say today is July-07-2020. 

We are looking for our Start date to be the July-01-2020 (1st default) and end to be July-07-2020 (dynamically picked).

Now, we want the number/stats for last month where the date range should be June-01-2020 to June-07-2020. 

Scenario 2: If it was march 30.

Then we want to compare the stats for last month within the date range from 1st to 30. Whereas February doesn't have 30 days, so it should pic the last date in February.
So, we would like the date range to be automatic. Where 1st of the month can be static but the last day(today) should be automatically selected to do the realtime comparison for that many number or dates.