cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JakeJack
Frequent Visitor

Help with a Date Question

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

 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@JakeJack , use this inplace of today

var _today = max(allselected(DailyProd), DailyProd[pdate])


Power BI Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!
Want To Learn Power BI | Beginners !! Advance Concepts !! Power BI For Tableau User !!

View solution in original post

3 REPLIES 3
Ailsa-msft
Community Support
Community Support

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

amitchandak
Super User
Super User

@JakeJack , use this inplace of today

var _today = max(allselected(DailyProd), DailyProd[pdate])


Power BI Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!
Want To Learn Power BI | Beginners !! Advance Concepts !! Power BI For Tableau User !!

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  

 

 

 

 

 

 

Helpful resources

Announcements
August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 24 without aka link and time 768x460.jpg

Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors