Help with a Date Question

[JakeJack]

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

[Anonymous]

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 · Accepted solution]

@JakeJack , use this inplace of today

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

[JakeJack]

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