Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Comparing date with max date in Power BI

Hi,

I have the below problem for which I am unable to find a solution:

I have got a text parameter in the dashboard based on which I have to calculate the max date. I created a measure column to get the max date.

Max Date = IF(Selection_Value = 1,
calculate(max('P&L'[Date]),filter(allselected('P&L'), 'P&L'[Actual Mtd] > 0)),
DATE(SELECTEDVALUE(Param_Year[Value]),SELECTEDVALUE(Param_Month[Value]),1))
 
Here Selection_Value is a parameter.
Now I want to compare the transcation dates in my table with this Max Date, but when I try to create a column calculation it gives me incorrect results as Max Date is a Measure column.
When I try to calcuate the datediff in Measure column it tells me that I need to aggregate the Date column. 
I am really frustrated at this point as this would have been a 10 minute work in Tableau but it is taking hours in Power BI.
Please help!
 
Need Help

2 Replies

  • DataNinja777's avatar
    DataNinja777
    Icon for Super User rankSuper User
    2 years ago

    Hi Anonymous 

    It appears from your dax formula that you are not using Calendar table and star schema data model.  In order to make everything smoother, I recommend that you restructure your data model and follow the data modeling best practice linking your fact able ('P&L'[Date] field) with your dimension table ('Calendar'[Date] field).  After restructuring your data model, things will be much easier and you do not need to write a long-winded formula for max date.  Your max date will just be something like MaxDate=max('Calendar'[Date]).  

    Best regards,

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi DataNinja777 ,

    Thank you for responding to my query.

    Please note that even if I create a calendar dimension table, I need the max date to be calculated basis a selection parameter.

    If my selection parameter is 'Latest' then I need the date where the SUM(Actuals) != 0 

    else if the selection parameter is 'Custom' then the date will be taken from 2 other parameter(Year and Month Selection).

    I need to create a measure column in this scenario to get the max date as custom columns dont read the parameter values in Power BI

    The next step is to compare every date in the data with the latest date so that I can filter the visuals only on latest date. I cannot use a custom column for this as it is not displaying correct date in latest date, but if I use a measure column that I have to specify a level of aggregation to the date which doesnt satisfy my purpose.

    Period Filter = IF(AND(DATEDIFF('P&L'[Latest Date],'P&L'[Date],YEAR) = 0 ,DATEDIFF('P&L'[Latest Date],'P&L'[Date],MONTH) = 0), TRUE,FALSE)

    Please help!