Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Visualize costs dynamically

Hi,   I have two columns which are costs and period. The period ranges from 2018 Jan to 2020 Jan. Each month I add new column to the excel source with new date and cost. I want to visualize the las...
Need Help
  • v-lionel-msft's avatar
    v-lionel-msft
    6 years ago

    Anonymous,

     

    I have a way.
    First sort [Period] column in ascending order, then, add a [index] column in Edit Queries.

    Second, create measures:

    Last12 = 
VAR x = 
CALCULATE(
    MAX(Sheet1[Index]),
    ALL(Sheet1)
)
RETURN
CALCULATE(
    MAX(Sheet1[Costs]),
    FILTER(
        Sheet1,
        [Index] <= x && [Index] >= x-12
))

Last12 - Last 24 = 
VAR x = 
CALCULATE(
    MAX(Sheet1[Index]),
    ALL(Sheet1)
)
RETURN
CALCULATE(
    MAX(Sheet1[Costs]),
    FILTER(
        Sheet1,
        [Index] <= x-12 && [Index] >= x-24
))

    As your data source's rows increase, the [Index] column will automatically increase, so the result is still right.

     

    Best regards,
    Lionel Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

amitchandak's avatar
amitchandak
Super User
6 years ago

One way is that you use the visual level date filter and use the relative date filter to have last 12 months, last 24 month including today.

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],max(Sales[Sales Date]),-12,Month))  
Rolling 24 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],max(Sales[Sales Date]),-24,Month))

 You can need to use date calendar for this

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi amitchandak,

     

    I do not know why but the first measure shows the whole period not the last 12 month, and it takes cumulative sum of the all periods. 

    I want the second measure to show the period between 24 months before last date and 12 month before the last date. Not whole 24 month before the last date. 

     

    Thanks!