Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## DAX Return the number of days in previous month

Hi,

I have been trying to tackel this DAX formula for a while now, it seems so simple but I cannot get it to work.

I'm have a table which list's out months start and end date, within that month there is a budget [Leads] and then a daily budget [Leads Daily Budget]. see below

Budget Sheet

The Multi row card below is what I'm trying to achive. but the budget figure is wrong.

I need to be able to calaculate the number of days in the previous month relative to today() then * by [Leads Daily Budget] for that month.  any assistance will be aprricated,

Dobby

2 ACCEPTED SOLUTIONS
Solution Sage

DAX has an EOMONTH command, use that in relation to today and offset it by -1 and -2 to get the end date of the last two months, it should be easy enough to datediff and adjust from there

Anonymous
Not applicable

This worked for me. I'll show my DAX below incase anyone gets a similar issues I created 3 measures,

Prv Month = EMONTH( TODAY(),-1)

Prv Month*2  = EMONTH( TODAY(),-2)

Days in Prev Month = DATEDIFF([Prv Month *2],[Prv Month],DAY)

This gives me the 31 days which there was in August

12 REPLIES 12
Super User
`last month day mul Sales = CALCULATE(SUM(Sales[Sales Amount])*CALCULATE(maxx(Sales,day(EOMONTH(DATEADD(Sales[Sales Date],-1,MONTH),0)))),DATESMTD('Date'[Date Filer])) `
Anonymous
Not applicable

Hi @Anonymous ,

Number of Days in last month = is it no of working days in last month or total number of days in lastmonth?

Anonymous
Not applicable

If I'm honest I was just looking for total days but if working days is a possibility it certainly would be better

Solution Sage

DAX has an EOMONTH command, use that in relation to today and offset it by -1 and -2 to get the end date of the last two months, it should be easy enough to datediff and adjust from there

Anonymous
Not applicable

This worked for me. I'll show my DAX below incase anyone gets a similar issues I created 3 measures,

Prv Month = EMONTH( TODAY(),-1)

Prv Month*2  = EMONTH( TODAY(),-2)

Days in Prev Month = DATEDIFF([Prv Month *2],[Prv Month],DAY)

This gives me the 31 days which there was in August

Anonymous
Not applicable

Hi,

I believe you would have got a date table if not add a table with the below dax

Dates =
ADDCOLUMNS (
CALENDARAUTO(),
"DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),
"Date ddd dd/mm/yyyy", FORMAT([Date], "ddd dd/mm/yyyy"),
"Year", YEAR ( [Date] ),
"Monthnumber", MONTH([Date]),
"YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ),
"YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ),
"MonthNameShort", FORMAT ( [Date], "mmm" ),
"MonthNameLong", FORMAT ( [Date], "mmmm" ),
"DayOfWeekNumber", WEEKDAY ( [Date] ),
"Month", MONTH([DATE]),
"DayOfWeek", FORMAT ( [Date], "dddd" ),
"DayOfWeekShort", FORMAT ( [Date], "ddd" ),
"Quarter", "Q" & FORMAT ( [Date], "Q" ),
"YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" ),
"YearMon", FORMAT ( [Date], "YYYY" ) & FORMAT ( [Date], "mmm" ),
"WeekNo", FORMAT([Date], "ww"),
"DayNo", FORMAT([Date], "d"),
"MonthLongYear", FORMAT ( [Date], "mmmm" ) & " " & FORMAT([Date], "YYYY"),
"DayNoMonthShort", FORMAT([Date], "ddd") & " " & FORMAT([Date], "d"),
"DayNoINT", DAY([Date]),
"DDMMYYYY", FORMAT([Date], "DD/MM/YYYY")
)

Then add a column to find if the day is a working day or not
Working day = IF(NOT(Dates[DayOfWeekNumber] IN {1,7}), "Y", "N")

Working days in Previous month =
var WorkingDays =
CALCULATE (
COUNTROWS ( DISTINCT ( Dates[Date] ) ),
Dates[WorkingDay] = "Y"
)
Return
CALCULATE (
WorkingDays,
FILTER (
Dates,
Dates[Year] = YEAR ( TODAY () )
&& Dates[Monthnumber] = MONTH ( TODAY () ) -1
), Dates[Date] < TODAY()
)

Hopefully this should work.

Anonymous
Not applicable

Hi @Anonymous ,

Thanks for taking the time to look into my issues, funfortuantly this didn't work! It returns a value of 3913... I copied your steps exactly as well (as in I created a new date table) and copied your code.

Anonymous
Not applicable

Hi @Anonymous ,

Just checking do you have a relationship set up between the New date table you have created and your fact table in the Model?

Regards,

Prime

Anonymous
Not applicable

Hi @Anonymous ,

Come to think of it I don't think I did! I have found the temp solution to this as posted above. I'll probably come back and look at you're method in a few months when refining to get working days instead of total days. thank you very much for the time invested in looking at this.

Regards

Dobby

Community Support

hi, @Anonymous

Sample data and your expected output would help tremendously.
Please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

and I think you could use Time-intelligence to create a measure to get it.

https://docs.microsoft.com/en-us/dax/time-intelligence-functions-dax

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable
Anonymous
Not applicable

Hi Lin,

Thanks for the reply, I did look at that forum but it wasn't quite right. please see my sample data.

Leads

 Id Name RecordTypeId Company Phone Lead Source Status OwnerId IsConverted ConvertedDate CreatedDate 00Q0O00000z627pUAA Fist Name, Surname null LekSCo null Not Selected Qualified 0050O000007hnwmQAA TRUE 02/11/2017 02/11/2018 00Q0O0000125Pw1UAE Fist Name, Surname 0120O0000007tDgQAI INITIO Biotech null Website Qualified 0050O000007kKR0QAM TRUE 11/09/2018 07/08/2019 00Q0O0000125u7yUAA Fist Name, Surname 0120O0000007tDgQAI Hall Analytical null Other Unqualified 0050O000007kKR0QAM FALSE null 12/08/2019 00Q0O0000125w9GUAQ Fist Name, Surname 0120O0000007tDgQAI Cyca Oncosolutions 0353 8729 22348 Website Unqualified 0050O000007kKR0QAM FALSE null 13/08/2019 00Q0O0000125yOhUAI Fist Name, Surname 0120O0000007tDgQAI Jon Willis null Website Unqualified 0050O000007kKR0QAM FALSE null 13/08/2019 00Q0O0000126CVrUAM Fist Name, Surname 0120O0000007tDgQAI Ipsen null Word of Mouth Unqualified 0050O000007kKR0QAM FALSE null 17/08/2019 00Q0O000013ZjgDUAS Fist Name, Surname 0120O0000007tDgQAI Allergen null Prospecting Qualified 0050O000007kKR0QAM TRUE 10/09/2018 26/06/2019 00Q0O000013Zji9UAC Fist Name, Surname 0120O0000007tDgQAI Sai Life Sciences +919949824242 Database Unqualified 0050O000007kKR0QAM FALSE null 26/06/2019 00Q0O000013ZjpAUAS Fist Name, Surname 0120O0000007tDgQAI EVD UK Holdings Limited null Prospecting Qualified 0050O000007kKR0QAM TRUE 26/06/2018 26/06/2019 00Q0O0000125PxOUAU Fist Name, Surname 0120O0000007tDgQAI Colliers null Agent Qualified 0050O000007kKSZQA2 TRUE 07/08/2018 07/08/2019 00Q0O0000125Q2sUAE Fist Name, Surname 0120O0000007tDiQAI Jonathan Watt null Agent Unqualified 0050O000007kKSZQA2 FALSE null 07/08/2019 00Q0O0000125Q3RUAU Fist Name, Surname 0120O0000007tDiQAI Chris Gell null Broker Unqualified 0050O000007kKSZQA2 FALSE null 07/08/2019 00Q0O0000125Q4PUAU Fist Name, Surname 0120O0000007tDiQAI Nicki Harvey 01213317588 Broker Unqualified 0050O000007kKSZQA2 FALSE null 07/08/2019 00Q0O0000125QDWUA2 Fist Name, Surname 0120O0000007tDgQAI Brook Financial Management Ltd null Broker Qualified 0050O000007kKSZQA2 TRUE 07/08/2018 07/08/2019 00Q0O0000125QICUA2 Fist Name, Surname 0120O0000007tDiQAI Ifrah james null Website Qualified 0050O000007kKSZQA2 TRUE 07/08/2018 07/08/2019 00Q0O0000125QiKUAU Fist Name, Surname 0120O0000007tDgQAI Cube Creative null Website Unqualified 0050O000007kKSZQA2 FALSE null 07/08/2019 00Q0O0000125QijUAE Fist Name, Surname 0120O0000007tDgQAI Montagu Evans 0207 866 6717 Agent Unqualified 0050O000007kKSZQA2 FALSE null 07/08/2019 00Q0O0000125QzEUAU Fist Name, Surname 0120O0000007tDiQAI NUJ null Website Qualified 0050O000007kKSZQA2 TRUE 07/08/2018 07/08/2019 00Q0O0000125R7DUAU Fist Name, Surname 0120O0000007tDiQAI Jacqueline Stokes 01512871342 Broker Unqualified 0050O000007kKSZQA2 FALSE null 07/08/2019

Leads Budget

 Start Date End Date Month FY Division Region Leads Leads Daily Budget Leads **bleep** 01/10/2018 31/10/2018 01/10/2018 2019 SciTech Alderley Park - SciTech 18 0.5806 18 01/11/2018 30/11/2018 01/11/2018 2019 SciTech Alderley Park - SciTech 18 0.6 36 01/12/2018 31/12/2018 01/12/2018 2019 SciTech Alderley Park - SciTech 18 0.5806 54 01/01/2019 31/01/2019 01/01/2019 2019 SciTech Alderley Park - SciTech 18 0.5806 72 01/02/2019 28/02/2019 01/02/2019 2019 SciTech Alderley Park - SciTech 18 0.6429 90 01/03/2019 31/03/2019 01/03/2019 2019 SciTech Alderley Park - SciTech 18 0.5806 108 01/04/2019 30/04/2019 01/04/2019 2019 SciTech Alderley Park - SciTech 18 0.6 126 01/05/2019 31/05/2019 01/05/2019 2019 SciTech Alderley Park - SciTech 18 0.5806 144 01/06/2019 30/06/2019 01/06/2019 2019 SciTech Alderley Park - SciTech 18 0.6 162 01/07/2019 31/07/2019 01/07/2019 2019 SciTech Alderley Park - SciTech 18 0.5806 180 01/08/2019 31/08/2019 01/08/2019 2019 SciTech Alderley Park - SciTech 18 0.5806 198 01/09/2019 30/09/2019 01/09/2019 2019 SciTech Alderley Park - SciTech 18 0.6 216 01/10/2018 31/10/2018 01/10/2018 2019 SciTech Manchester City Centre - Circle Square 13.5833 0.4382 13.5833 01/11/2018 30/11/2018 01/11/2018 2019 SciTech Manchester City Centre - Circle Square 13.5833 0.4528 27.1666 01/12/2018 31/12/2018 01/12/2018 2019 SciTech Manchester City Centre - Circle Square 13.5833 0.4382 40.7499 01/01/2019 31/01/2019 01/01/2019 2019 SciTech Manchester City Centre - Circle Square 13.5833 0.4382 54.3332 01/02/2019 28/02/2019 01/02/2019 2019 SciTech Manchester City Centre - Circle Square 13.5833 0.4851 67.9165 01/03/2019 31/03/2019 01/03/2019 2019 SciTech Manchester City Centre - Circle Square 13.5833 0.4382 81.4998 01/04/2019 30/04/2019 01/04/2019 2019 SciTech Manchester City Centre - Circle Square 13.5833 0.4528 95.0831 01/05/2019 31/05/2019 01/05/2019 2019 SciTech Manchester City Centre - Circle Square 13.5833 0.4382 108.6664 01/06/2019 30/06/2019 01/06/2019 2019 SciTech Manchester City Centre - Circle Square 13.5833 0.4528 122.2497 01/07/2019 31/07/2019 01/07/2019 2019 SciTech Manchester City Centre - Circle Square 13.5833 0.4382 135.833 01/08/2019 31/08/2019 01/08/2019 2019 SciTech Manchester City Centre - Circle Square 13.5833 0.4382 149.4163 01/09/2019 30/09/2019 01/09/2019 2019 SciTech Manchester City Centre - Circle Square 13.5833 0.4528 162.9996

The expeced results for a previous month would be

15         Leads

31.58    Budget

-16.58  Variance

The calculations should be

Leads = Number of records in the 'leads' with a [created date] of last month

Budget = Calculate( Sum('Leads Budget'[Leads Daily Budget]) *Number of day in last month if it relates to that month)   // Number of days in last month is the figure I cannot get.

Variance = Sum (Leads - Budget)

I hope this is enough information

Dobby

## Helpful resources

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors