Static columns for last year info

[ghammond09]

Hi,

I would like to  create a graph with 12 static columns for last year and then this year's data is added when I select the current month.  The main issue is having a fixed start date for the graph (our financial year runs from February to January, no idea why the company is 75 years old.) and so we need to show Feb 19 to Jan 20 as the last year info and then from Feb 20 as this year.

I have a dates table and a data table with thousands of lines into going back several years.  I will not worry about the budget at the moment as I can create a new table for that info. 

Thanks,

Glynn

[v-juanli-msft]

Hi @ghammond09 

Create a date table

date =
ADDCOLUMNS (
    CALENDARAUTO (),
    "year", YEAR ( [Date] ),
    "month", MONTH ( [Date] ),
    "monthname", FORMAT ( [Date], "Mmm" ),
    "year-month", FORMAT ( [Date], "yyyy-mm" )
)

Add columns

adjusted month = IF([month]>=2,[month]-1,[month]+11)

adjusted year = IF([month]>=2,[year],[year]-1)

adjusted year-month = [adjusted year]&"-"&[adjusted month]

Create measures

lastyear =
CALCULATE (
    SUM ( 'Table'[value] ),
    FILTER (
        'Table',
        'Table'[date]
            >= DATE ( YEAR ( TODAY () ) - 1, 2, 1 )
            && 'Table'[date] <= DATE ( YEAR ( TODAY () ), 1, 31 )
    )
)

this year =
CALCULATE (
    SUM ( 'Table'[value] ),
    FILTER (
        'Table',
        'Table'[date] >= DATE ( YEAR ( TODAY () ), 2, 1 )
            && 'Table'[date] <= TODAY ()
    )
)

download my file from the following link:

https://qiuyunus-my.sharepoint.com/:u:/g/personal/admin_qiuyunus_onmicrosoft_com/Ee4kTyqWnc1MmLC0XR8...

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ghammond09]

Hi Maggie,

Thank you for the reply.  I will test it with my data. 

I am not sure what the "adjusted months" columns are for.  They do not appear  to be referenced anywhere else or used in the graphs.

Regards,

Glynn

[amitchandak]

@ghammond09 , I did not get the exact problem.  You can always have a calendar that starts from any month.

And in time intelligence YTD , you can use any year end date. For last year same date and same month you can use

last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"1/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"1/31"))
This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"1/31"))
Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"1/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"1/31"))

Please find Link to Feb-Jan Calendar, You can create some required columns based on that

https://www.dropbox.com/s/wmo0ym6ne8ee4cu/Feb_2_Jan_Qtr_Cal.pbix?dl=0

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/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.

[ghammond09]

Hi,

Thanks for the dataset.  I will have a look with that and what I can do.

My current data set has date fields as follows.

Thank,

Glynn

[amitchandak]

@ghammond09 , You need to try out and let us know exact issues. \

This one might also help

https://www.youtube.com/watch?v=duMSovyosXE

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[ghammond09]

Hi amitchandak,

That was an interesting video but it made a graph that was twelve months prior to the current date.  Not quite what I am after.

My data is very simple.  This is the MTD value:

28/2/2019 1595

31/3/2019 1899

30/4/2019 1695

31/5/2019 2646

30/6/2019 2014

31/7/2019 2300

31/8/2019 2048

30/9/2019 1758

31/10/2019 1822

30/11/2019 1999

31/12/2019 1291

31/1/2020 675

29/2/2020 1604

31/3/2020 1891

30/4/2020 1412

31/5/2020 1278

The desired result is to end up with a graph that looks like what I showed in my original post.  I can get a graph that is 16 columns wide but not one that has a fixed start point of February 2019, twelve columns for last year and then four for this year that updates itself with a new column when I change my date selector.

Thank you for all your help.  I am new at this so please bear with with me.

Glynn

[ryan_mayu]

@ghammond09 

I think you need to create new fiscal month ,fiscal year columns in your date table and put them in the axis.