Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

Visualizing last twelve months and annualize revenue

Hi all, 

I have data set with two columns: revenue of good sold and date sold.

Date range from 1.1.2016 to 31.07.2019

 

Now I want to have a column chart that shows:

- Total revenue for the last 3 years: 2016, 2017, 2018

 

- Projected revenue for 2019 calculated as: (Total revenue 2019 up to date)/(number of months up to date)*12

 As the current date is at Jule so:

 Projected revenue 2019 will be: (Total revenue of 2019 from 1.1.2019 to latest date)/7*12

 

- Last twelve months revenue= Total revenue for 12 lastest months up to the current month.

 As the current month is July 2019, this column will show total revenue from Aug.2018~Jul 2019. 

Any month the data will be updated automatically so I wish to have a function that can visualize the latest data at the end of each month. 

I tried many ways but cannot include all the things I want into the same graph/chart. 

I desperately look for your help and highly appreciate if you could spend some time to help me. 

Wish you all the best,



AnDy.

 

Data sample can be seen https://1drv.ms/x/s!AsZJp0cEbKG7gy3XJYnMfUjBdTwy

1 ACCEPTED SOLUTION

See, you think it sounds simple but the asymetry (year, year, year, ltm) makes it interesting. 

 

We have to make a table that holds the years and an 'LTM' entry:

Chart Axis = 
UNION(
    DISTINCT(
        SELECTCOLUMNS(Dates,
        "Period",format(Dates[Year],"0000"))
    ),
    ROW("Period","LTM")
)

We also need an LTM measure

LTM = CALCULATE(
    [Total Amount],
    Dates[Month Offset] >= -11 && Dates[Month Offset] <= 0)

And finally a measure to use in our chart.

Chart Amount = 
SUMX ( 'Chart Axis',
VAR CurrentIterator = 'Chart Axis'[Period]
RETURN 
    IF (
        'Chart Axis'[Period] <> "LTM",
        CALCULATE(
            [Total Amount],
            Dates[Year] = VALUE(CurrentIterator)
        ),
        [LTM]
    )
)

These we can use to get our chart.

ltmchart.jpg

Here is a link to the updated file.  https://www.dropbox.com/s/bzljhhyx4owt7sb/Practice%20power%20BI.pbix?dl=0

Many thanks to Lukas Lotters for his post that inspired this solution and which I have used many other places.

https://www.oraylis.de/blog/show-top-n-and-rest-in-power-bi

View solution in original post

8 REPLIES 8
jdbuchanan71
Super User
Super User

Hello @Anonymous ,

The first thing is to load the data sample to Power BI.  Next add a date table to the model and join it to your data table.

Here is some DAX code you can use to create a date table in your model to help us get started.

Dates = 
VAR DateRange = CALENDARAUTO()

RETURN
ADDCOLUMNS(
    DateRange,
    "Year",YEAR ( [Date] ),
    "Month", FORMAT ( [Date], "mmmm" ),
    "MonthNum", MONTH ( [Date] ),
    "Month Year", FORMAT ( [Date], "mmm-yyyy"),
    "MonthYearNum", YEAR ( [Date] ) * 100 + MONTH ( [Date] ),
    "Quarter Year", "Q" & FORMAT ( [Date], "q-yyyy" ),
    "QtrYearNum", YEAR ( [Date] ) * 100 + VALUE ( FORMAT ( [Date], "q" ) )
)

Please reply with a link to the .pbix file once you have that created.

Anonymous
Not applicable

@jdbuchanan71 

I have upload the data with date created here: https://1drv.ms/u/s!AsZJp0cEbKG7gy_rszkBsHaJ8vX3?e=Hdn72G


I am looking forward to your help.

Many thanks,

AnDy. 

Hello @Anonymous 

I have updated the model to link the dates table to your data table and added in measures for

  • Amount
  • YTD Amount
  • Trailing 12 month Amount
  • Projection
  • YTD future projection

The file is here: https://www.dropbox.com/s/bzljhhyx4owt7sb/Practice%20power%20BI.pbix?dl=0

PowerBIPractice.jpg

I am worried that you now have a model that you won't have any idea how to make changes though. 

Spend some time reading about modeling and studying.  There are lots of good resources, for example
Data modeling: https://www.red-gate.com/simple-talk/sql/bi/power-bi-data-modelling/

Time Intelligence: https://www.sqlbi.com/articles/time-intelligence-in-power-bi-desktop/

 

Anonymous
Not applicable

Hi @jdbuchanan71 ,


I deeply appreciate your help. It looks amazing but more than I need. 

 

Indeed, I just need a simple graph as I can make via Exel here:

 

https://1drv.ms/x/s!AsZJp0cEbKG7gzAnpOXM7W8lW3Gj

 

I do not know how can I calculate a new table of data then I can use to make chart as I do in excel 😞 


Many thanks for sharing the link for me to improve my learning too.

 

 

See, you think it sounds simple but the asymetry (year, year, year, ltm) makes it interesting. 

 

We have to make a table that holds the years and an 'LTM' entry:

Chart Axis = 
UNION(
    DISTINCT(
        SELECTCOLUMNS(Dates,
        "Period",format(Dates[Year],"0000"))
    ),
    ROW("Period","LTM")
)

We also need an LTM measure

LTM = CALCULATE(
    [Total Amount],
    Dates[Month Offset] >= -11 && Dates[Month Offset] <= 0)

And finally a measure to use in our chart.

Chart Amount = 
SUMX ( 'Chart Axis',
VAR CurrentIterator = 'Chart Axis'[Period]
RETURN 
    IF (
        'Chart Axis'[Period] <> "LTM",
        CALCULATE(
            [Total Amount],
            Dates[Year] = VALUE(CurrentIterator)
        ),
        [LTM]
    )
)

These we can use to get our chart.

ltmchart.jpg

Here is a link to the updated file.  https://www.dropbox.com/s/bzljhhyx4owt7sb/Practice%20power%20BI.pbix?dl=0

Many thanks to Lukas Lotters for his post that inspired this solution and which I have used many other places.

https://www.oraylis.de/blog/show-top-n-and-rest-in-power-bi

Anonymous
Not applicable

Hi @jdbuchanan71 

 

I have a question, why do we need to create a table of date separatly with the original data set? these dates seem to be generated automatically, then doesnt it affect the preciseness of result ?

 

Besides, what if I want to add 1 more  customized asymetric measure to the chart? say like the topic owner demand of adding the projected revenue of 2019 to her chart?

 

Many thanks !

The custom table is there for the same reason you have it in your excel file.

yearltmitems.jpg

 

It gives you a single set of values to pull into the chart axis.  If the user wanted to add projection you would need to add projection to the code that creates the custom table, add a measure to calc the projection and modify the chart code to pull the projection measure.  That is why I said it sounds simple but is tricky because of what needs to happen to form the data the way you need it.

I may be oversimplifying the solution but wouldn't a measure including DATESINPERIOD do what you request?

 

CALCULATE(<total revenue measure name>, DATESINPERIOD(Calendar[Date], MAX(Calendar[Date]), -12, MONTH))

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.