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])

View solution in original post

3 REPLIES 3
v-yetao1-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])

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
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Top Solution Authors