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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
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 SheetBudget Sheet

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

 

multi row card.PNG

 

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

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

View solution in original post

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 

View solution in original post

12 REPLIES 12
amitchandak
Super User
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

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

v-lili6-msft
Community Support
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

IdNameRecordTypeIdCompanyPhoneLead SourceStatusOwnerIdIsConvertedConvertedDateCreatedDate
00Q0O00000z627pUAAFist Name, SurnamenullLekSConullNot SelectedQualified0050O000007hnwmQAATRUE02/11/201702/11/2018
00Q0O0000125Pw1UAEFist Name, Surname0120O0000007tDgQAIINITIO BiotechnullWebsiteQualified0050O000007kKR0QAMTRUE11/09/201807/08/2019
00Q0O0000125u7yUAAFist Name, Surname0120O0000007tDgQAIHall AnalyticalnullOtherUnqualified0050O000007kKR0QAMFALSEnull12/08/2019
00Q0O0000125w9GUAQFist Name, Surname0120O0000007tDgQAICyca Oncosolutions0353 8729 22348WebsiteUnqualified0050O000007kKR0QAMFALSEnull13/08/2019
00Q0O0000125yOhUAIFist Name, Surname0120O0000007tDgQAIJon WillisnullWebsiteUnqualified0050O000007kKR0QAMFALSEnull13/08/2019
00Q0O0000126CVrUAMFist Name, Surname0120O0000007tDgQAIIpsennullWord of MouthUnqualified0050O000007kKR0QAMFALSEnull17/08/2019
00Q0O000013ZjgDUASFist Name, Surname0120O0000007tDgQAIAllergennullProspectingQualified0050O000007kKR0QAMTRUE10/09/201826/06/2019
00Q0O000013Zji9UACFist Name, Surname0120O0000007tDgQAISai Life Sciences+919949824242DatabaseUnqualified0050O000007kKR0QAMFALSEnull26/06/2019
00Q0O000013ZjpAUASFist Name, Surname0120O0000007tDgQAIEVD UK Holdings LimitednullProspectingQualified0050O000007kKR0QAMTRUE26/06/201826/06/2019
00Q0O0000125PxOUAUFist Name, Surname0120O0000007tDgQAIColliersnullAgentQualified0050O000007kKSZQA2TRUE07/08/201807/08/2019
00Q0O0000125Q2sUAEFist Name, Surname0120O0000007tDiQAIJonathan WattnullAgentUnqualified0050O000007kKSZQA2FALSEnull07/08/2019
00Q0O0000125Q3RUAUFist Name, Surname0120O0000007tDiQAIChris GellnullBrokerUnqualified0050O000007kKSZQA2FALSEnull07/08/2019
00Q0O0000125Q4PUAUFist Name, Surname0120O0000007tDiQAINicki Harvey01213317588BrokerUnqualified0050O000007kKSZQA2FALSEnull07/08/2019
00Q0O0000125QDWUA2Fist Name, Surname0120O0000007tDgQAIBrook Financial Management LtdnullBrokerQualified0050O000007kKSZQA2TRUE07/08/201807/08/2019
00Q0O0000125QICUA2Fist Name, Surname0120O0000007tDiQAIIfrah jamesnullWebsiteQualified0050O000007kKSZQA2TRUE07/08/201807/08/2019
00Q0O0000125QiKUAUFist Name, Surname0120O0000007tDgQAICube CreativenullWebsiteUnqualified0050O000007kKSZQA2FALSEnull07/08/2019
00Q0O0000125QijUAEFist Name, Surname0120O0000007tDgQAIMontagu Evans0207 866 6717AgentUnqualified0050O000007kKSZQA2FALSEnull07/08/2019
00Q0O0000125QzEUAUFist Name, Surname0120O0000007tDiQAINUJnullWebsiteQualified0050O000007kKSZQA2TRUE07/08/201807/08/2019
00Q0O0000125R7DUAUFist Name, Surname0120O0000007tDiQAIJacqueline Stokes01512871342BrokerUnqualified0050O000007kKSZQA2FALSEnull07/08/2019

 

Leads Budget

Start DateEnd DateMonthFYDivisionRegionLeadsLeads Daily BudgetLeads **bleep**
01/10/201831/10/201801/10/20182019SciTechAlderley Park - SciTech180.580618
01/11/201830/11/201801/11/20182019SciTechAlderley Park - SciTech180.636
01/12/201831/12/201801/12/20182019SciTechAlderley Park - SciTech180.580654
01/01/201931/01/201901/01/20192019SciTechAlderley Park - SciTech180.580672
01/02/201928/02/201901/02/20192019SciTechAlderley Park - SciTech180.642990
01/03/201931/03/201901/03/20192019SciTechAlderley Park - SciTech180.5806108
01/04/201930/04/201901/04/20192019SciTechAlderley Park - SciTech180.6126
01/05/201931/05/201901/05/20192019SciTechAlderley Park - SciTech180.5806144
01/06/201930/06/201901/06/20192019SciTechAlderley Park - SciTech180.6162
01/07/201931/07/201901/07/20192019SciTechAlderley Park - SciTech180.5806180
01/08/201931/08/201901/08/20192019SciTechAlderley Park - SciTech180.5806198
01/09/201930/09/201901/09/20192019SciTechAlderley Park - SciTech180.6216
01/10/201831/10/201801/10/20182019SciTechManchester City Centre - Circle Square13.58330.438213.5833
01/11/201830/11/201801/11/20182019SciTechManchester City Centre - Circle Square13.58330.452827.1666
01/12/201831/12/201801/12/20182019SciTechManchester City Centre - Circle Square13.58330.438240.7499
01/01/201931/01/201901/01/20192019SciTechManchester City Centre - Circle Square13.58330.438254.3332
01/02/201928/02/201901/02/20192019SciTechManchester City Centre - Circle Square13.58330.485167.9165
01/03/201931/03/201901/03/20192019SciTechManchester City Centre - Circle Square13.58330.438281.4998
01/04/201930/04/201901/04/20192019SciTechManchester City Centre - Circle Square13.58330.452895.0831
01/05/201931/05/201901/05/20192019SciTechManchester City Centre - Circle Square13.58330.4382108.6664
01/06/201930/06/201901/06/20192019SciTechManchester City Centre - Circle Square13.58330.4528122.2497
01/07/201931/07/201901/07/20192019SciTechManchester City Centre - Circle Square13.58330.4382135.833
01/08/201931/08/201901/08/20192019SciTechManchester City Centre - Circle Square13.58330.4382149.4163
01/09/201930/09/201901/09/20192019SciTechManchester City Centre - Circle Square13.58330.4528162.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
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.