Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
Hi all
My first post.
I'm a newbe to Power BI coming from database development and have been scratching my head for a few hours over what I reckoned should be something simple.
I've created the following measure to calculate the daily production values which works:
Sales CM =
VAR MaxPDate = today()
VAR CurrentMonth = MONTH( MaxPDate )
VAR CurrentYear = YEAR( MaxPDate )
VAR LastDay = EOMONTH( DATE( CurrentYear, CurrentMonth, 1 ), 0 )
VAR FirstDay = DATE( CurrentYear, CurrentMonth, 1 )
RETURN
CALCULATE(
[Total Sales],
FILTER(
Dates,
Dates[CurDate] >= FirstDay &&
Dates[CurDate] <= LastDay
)
)
However I wish to replace the today() function with
max(DailyProd[pdate])
which is the max production date in the table.
The field in the DailyProd table is a date type - not datetime.
When I use max(DailyProd[pdate]) instead of today() in the VAR MaxPDate I get every date in the Dates table therefore the filtering isn't working.
I'm sure this is blinding obvious but I'd appreciate any help.
Thanks
Jake
Solved! Go to Solution.
@JakeJack , use this inplace of today
var _today = max(allselected(DailyProd), DailyProd[pdate])
Hi @JakeJack
MAX : Returns the largest value in a column, or the larger value between two scalar expressions. Ignores logical values. Strings are compared according to alphabetical order.
MAXX : Returns the largest value that results from evaluating an expression for each row of a table. Strings are compared according to alphabetical order.
ALLSELECTED : Removes context filters from columns and rows in the current query, while retaining all other context filters or explicit filters.
You can get a detail understanding through these links below .
https://docs.microsoft.com/en-us/dax/maxx-function-dax
https://docs.microsoft.com/en-us/dax/max-function-dax
https://docs.microsoft.com/en-us/dax/allselected-function-dax
Best Regard
Community Support Team _ Ailsa Tao
@JakeJack , use this inplace of today
var _today = max(allselected(DailyProd), DailyProd[pdate])
Hi @amitchandak
max(allselected(DailyProd), DailyProd[pdate])
didn't work but maxx(allselected(DailyProd), DailyProd[pdate]) did!
I'm not sure as to why I need to use allselected eg if I create a measure Max Prod Date = calculate(max(DailyProd[pdate]),DailyProd[day_sales]>0) and place this measure on a page I do get the maxium production date. So I've no idea why I can't just use this scalar in subsequent measures but I'll read up on it.
Many thanks
Jake
User | Count |
---|---|
121 | |
76 | |
63 | |
51 | |
50 |
User | Count |
---|---|
175 | |
125 | |
60 | |
60 | |
58 |