DAX Return the number of days in previous month

[Anonymous]

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

[jthomson · Accepted solution]

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

[amitchandak]

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]

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]

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

[jthomson · Accepted solution]

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 · Accepted solution]

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]

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]

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]

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]

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

[v-lili6-msft]

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

[Anonymous]

@v-lili6-msft 

[Anonymous]

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