Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Hannah Denning]

Hello

I have numerous occasions when I need to count the number of incidents, events, actions raised etc in the last calendar month, last calendar quarter and other time spans. I do not need it for all dates/months in the date table just once based on TODAYS date.
So, for example, this month is June 2020, I would need the count of May 2020 or if quarter would need it for Q1 2020. 

Is it possible to achieve this using DATEADD function?

I have two ways that I currently do this. I have a month offset column and a quarter offset column in my dates table but the result does not give clean date for the last calendar year / quarter. I took the code for creating these two columns from a website (Avi Singh, I think) but suspect that it is calculating last 30 days (or specific number of days) which is why it doesn’t sit neatly into one calendar month

I am using the below DAX calculation for previous month count. This works fine for month but I can’t get it to work for previous quarter by changing the MONTH function to QUARTER.

Safety Performance No. Events Last Month = var current_month= MONTH(TODAY()) return CALCULATE(COUNT(‘Safety Performance'[Event]),FILTER(‘Safety Performance’,MONTH(‘Safety Performance'[Date])=current_month -1))

Does anyone know of any better / smarter ways to achieve this? and how I could get the results for last quarter? Could you also let me know what the DAX calculation would be if the relationship was inactive? For one of my FACT dates I will need to generate this measure against two date columns – due date and date raised. so number due last month and last quarter and number raised last month/last quarter

I have a date table in my model that is marked as the Date Table. One to many relationships are in place between the date column in the Date Table and the date columns in the FACT table for which I need to calculate this measure for .
any help very much appreciated
Thanks

——————————
Hannah
——————————

[Lutz Bendlin]

Your monthly formula example will fail in January.

You are on the right track with DATEADD – that allows you to specify the interval (month, quarter, year) and the stride ( one month back, two years back etc).

You also have the Dates table which is very good.

Have a look at the Quick Measure function in Power BI – it provides some great examples for the kinds of computations you want to do.

——————————
Lutz
——————————
——————————————-

[Hannah Denning]

Hi Lutz

Thank you for your reply.

I might be missing something but having looked at the Quick Measures (already looked at but looked again to double check) I can’t see anything that would work for count of previous month/quarter etc..  I can see year to date and month on month, for example but nothing that would generate the result that I am looking for.

Have I missed something?

Thanks
Hannah

——————————
Hannah Denning
——————————
——————————————-

[Lutz Bendlin]

I just tried the “Month Over Month”  quick measure, and it’s pretty good.

MoM% = 
VAR __PREV_MONTH = CALCULATE(SUM([column]), DATEADD('Dates'[date], -1, MONTH))
RETURN
	DIVIDE(SUM([column]) - __PREV_MONTH, __PREV_MONTH)?

——————————
Lutz
——————————
——————————————-

[John Thomas]

Hannah,

Check out DAX Cookbook by Greg Deckler.  He has some awesome recipes for business application in it.  Here’s a sample for “Previous Month”:

Previous Month = 
/*See Greg Deckler's book "DAX Cookbook".  It's on Amazon with everything else in the world.
* This uses a table named R12_Sales.  In it are the following Columns:
* Date - Date Column
* Value - Sales Value (USD)
* Year - Year of Date Column
* Month - Month Name of Date Column
* MonthNo - (hiden in report view) Month Number of Date Column (use to sort Month)
* Note:
*  Modify DAX Code below to fit your table name and columns.  You may also modify
*  to accomodate a Calendar Table instead of a Sales only table.
*/ 
    VAR _Date = MAX('R12_Sales'[Date])
    VAR _Year = YEAR(_Date)
    VAR _Month = MONTH(_Date)
    VAR _LookupYear = IF(_Month=1,_Year-1,_Year)
    VAR _LookupMonth = IF(_Month=1,12,_Month-1)
    VAR _Table = ALL('R12_Sales')
    VAR _LastMonth = 
        FILTER(
            _Table,
            YEAR([Date]) = _LookupYear && 
            MONTH([Date]) = _LookupMonth &&
            NOT(ISBLANK([Value]))
        )
    VAR _LastDayLastMonth = DAY(MAXX(_LastMonth,[Date]))
    VAR _ThisMonth = 
        FILTER(
            _Table,
            YEAR(_Date)=_Year && 
            MONTH(_Date)=_Month &&
            NOT(ISBLANK([Value]))
        )
    VAR _Rows = COUNTROWS(_ThisMonth)
    VAR _CurrentRow = 
        COUNTROWS(
            FILTER(
                _ThisMonth,
                [Date] <= _Date
            )
        )
    VAR _LookupDay = INT(_LastDayLastMonth * _CurrentRow / _Rows)
RETURN
    SUMX(
        FILTER(
            _LastMonth,
            DAY([Date]) <= _LookupDay
        ),
        [Value]
    )?

The above formula will take into consideration a “partial” current month.  If today is the 15th for example, you don’t want to compare your current month’s sales to the full month’s previous sales.  You can modify as needed…

Best of luck!

——————————
John Thomas
Huntersville NC
——————————
——————————————-