The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the August 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
109 | |
80 | |
77 | |
46 | |
39 |
User | Count |
---|---|
137 | |
108 | |
64 | |
64 | |
53 |