Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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
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
Solved! Go to 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
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)
This gives me the 31 days which there was in August
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]))
Hi @Anonymous ,
Number of Days in last month = is it no of working days in last month or total number of days in lastmonth?
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
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)
This gives me the 31 days which there was in August
Hi,
I believe you would have got a date table if not add a table with the below dax
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.
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
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
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
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
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
92 | |
83 | |
71 | |
49 |
User | Count |
---|---|
143 | |
120 | |
110 | |
59 | |
57 |