Forum Discussion

rhinorocks's avatar
rhinorocks
Frequent Visitor
4 years ago
Solved

Getting Maximum Date after slicing it from Month Number

Hi All,

I am new to power BI and need some help conceptually. 

 

Concept Question 1 

I have a data model where I have data from sales, collection, customer etc. I need a measure in which I can get the maximum date in the calendar table even if it is sliced or diced by month numbers. 

Means - If the maximum date in the calendar table is 31/01/2022 then my measure must show maximum date as 31/1/2022 even if I create a matrix report with Month numbers in a column. 

I have tried this formula and it works (I have hardcoded the year as 2022) - 

2022 MaxDate =
VAR _MaxDate =
FILTER(
All(
'Calendar'[Date]),
AND(
'Calendar'[Date] = Max('Calendar'[Date]),
Year('Calendar'[Date]) = 2022
)
)
RETURN
_MaxDate
 
But I want a measure in which I do not have to hardcode the 2022. It should automatically catch the Year as 2022. 
 
Concept Question -2 
In the measure I have used All('Calendar'[Date]) but when I use Filter('Calendar') then the measure gives an error of not finding a scalar value. Why is this happening? 
 
Would love to get your help of this. 
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi PaulDBrown 

    I posted this question from an account which I made this morning but could not track the credentials. This time I have noted the credentials. 

     

    My issue was - I have a Matrix table in which month number is one of the column and sales of current year and previous years are 2 more columns. As the current year of 2022 has 2 month only, sales of 2 months of 2022 is correct but for rest 10 months the sales in 2022 is showing the results of 2021. But I realised that the outer filter is not replaced properly. So I used keepfilters and the issue is solved. The formula is - 

     

    2022 MaxYear =
    VAR _MaxYear =
    CALCULATE(Max('Calendar'[Year]), All('Calendar'))
    VAR _2022Sales =
    CALCULATE(
    [Total Secondary Sales],
    KEEPFILTERS('Calendar'[Year] = _MaxYear)
    )
    RETURN
    _2022Sales
     
    As I do not have the credentials of the last account, please mark this as solved.

3 Replies

  • PaulDBrown's avatar
    PaulDBrown
    Community Champion
    4 years ago

    If you just want the max date in the calendar table you can use this measure:

    Max date = CALCULATE (MAX(Calendar [Date]), ALL(Calendar))

     

    the reason you are getting the error is because FILTER returns a table (even if only contains 1 value). Measures must return a scalar value so you need to use CALCULATE & an expression to return a single value.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi PaulDBrown 

    I posted this question from an account which I made this morning but could not track the credentials. This time I have noted the credentials. 

     

    My issue was - I have a Matrix table in which month number is one of the column and sales of current year and previous years are 2 more columns. As the current year of 2022 has 2 month only, sales of 2 months of 2022 is correct but for rest 10 months the sales in 2022 is showing the results of 2021. But I realised that the outer filter is not replaced properly. So I used keepfilters and the issue is solved. The formula is - 

     

    2022 MaxYear =
    VAR _MaxYear =
    CALCULATE(Max('Calendar'[Year]), All('Calendar'))
    VAR _2022Sales =
    CALCULATE(
    [Total Secondary Sales],
    KEEPFILTERS('Calendar'[Year] = _MaxYear)
    )
    RETURN
    _2022Sales
     
    As I do not have the credentials of the last account, please mark this as solved.