Sales Yr1 converted to Calendar Year Sales

[forti4040]

Hello All,

I am using an Access DB that has products names on each row, then columns that indicate Units Sold for Year 1, Year 2, and Year 3. I also have a First Shipment date on each row. 

I'm trying to generate a matrix/table to show fiscal sales. So far I've manually created new columns with if statements along the lines of:

Units_2020 = If (YEAR(Ship Date)=2020,Units+Yr1

Units_2021 = If (YEAR(Ship Date)=2020,Units+Yr2, If (YEAR(Ship Date)=2021,Units+Yr1

Is there a more efficient way of doing this? I'm having to repeat these steps for Units, Sales, Margin, etc...and for every year!

Thanks in advance!

[danextian]

Hi @forti4040 ,

You can create a calculated column to indicate the fiscal year the ship date is in something like below:

FiscalYear =
IF (
    MONTH ( Table[ShipDate] ) >= 7,
    YEAR ( Table[ShipDate] ) + 1,
    YEAR ( Table[ShipDate] )
)

 But I would usually use a separate Dates table instead and just create a relationship with the Date column in my fact table. Here's a sample calculated Dates table: 

Dates =
VAR __START =
    DATE ( 2018, 1, 1 )
VAR __END =
    TODAY ()
VAR __FY_START = 7
VAR __CALENDAR =
    CALENDAR ( __START, __END )
RETURN
    ADDCOLUMNS (
        __CALENDAR,
        "Month Name", FORMAT ( [Date], "mmmm" ),
        //column to sort Month Name by
        "Month Number", MONTH ( [Date] ),
        "Calendar Year", YEAR ( [Date] ),
        "Fiscal Year", IF ( MONTH ( [Date] ) >= __FY_START, YEAR ( [Date] ) + 1, YEAR ( [Date] ) ),
        "Calendar Month & Year", FORMAT ( [Date], "mmm yyyy" ),
        //column to sort Calendar Month & Year by
        "YRMO", FORMAT ( [Date], "YYYYMM" )
    )

[forti4040]

Hello All,

Thank you for all the replies. I'm sharing a sample of ficticious data to show what I'm trying to accomplish. 

https://www.dropbox.com/s/esnx46n6gl1vs3f/PBISample.xlsx?dl=0

Image of data below as well

[edhans]

Here is what I did. You could spend some time with column headings to get exactly what you had in the Excel file, but the below is pretty flexible and self explanatory.

The M code is across 4-5 queries, so the best bet is to just look at my PBIX file and see what I did. This is the Power Query model though. Only Sales and Date tables are loaded into the DAX model.

Here is the PBIX file. If you have any questions about the PQ transformations, ping back.

[forti4040]

@edhans ,

Thank you for the extremely well thought out and explained response. I've downloaded the file and walked through it step by step. I believe I see all the steps you've done and how you got to your final result. I'm going to work on my file over the next few days as I get time to see if I can implement this. Very much appreciate you taking the time to help!

[edhans]

Great! Ping back with any questions. Once done, mark it as the solution so we know it is answered. 

[forti4040]

@edhans ,

I started working on this last night and something I noticed is that I have a bit of uncleaned data for some Date entries. So when you are looking for the minimum calculated date I'm getting a value of 1890. Is there a way to adjust this with an if statement or something that says, use the minimum as long as it's greater than 2019 (or something similar)? That way my table (I haven't created that yet) doesn't show headings starting all the way back in the 1800's..

[edhans · Accepted solution]

Yes. Go to the varStartDate query in my model and go to the "Source" row.

Everything works off of the "Year" column so add a filter on that first row to force it to use whatever minimium you want. Rather than hardcoding though, you could use something like "Only return the dates in the last three years if there is something older".

1. Create a basic filter with any date. You'll get something like
   1. = Table.SelectRows(Source, each [Year] > #date(2020, 1, 15))
2. Change #date(2020,1,15) to something liket this:

= Table.SelectRows(Source, each [Year] > 
(if 
     List.Min(Source[Year]) < Date.AddYears(DateTime.Date(DateTime.LocalNow()), -3)
     then #date(Date.Year(Date.AddYears(DateTime.LocalNow(),-3)),1,1)
     else List.Min(Source[Year]))
)

This says:

1. If the minimum date in the [Year] column is less than (today-3 years) then
2. use the the year of (today-3 years) and back it up to Jan 1 of that year,
3. else use the minimum date from the Year column.

This is now in my PBIX file if you want to look at it. Just hit the link above again to get the latest copy. There are other ways to do this, like rather than filtering just using the List.Min and then doing some if/then/else logic there. And probably 10 other ways as well. 😀 Power Query is like Excel. If you can do it one way, you can do it many ways.

[v-jayw-msft]

Hi @forti4040 ,

I'm not quite understand your formula, what is "Units" in your formula and what's the relationship between "First Shipment date" and "Year1" as well as "Year2", "Year3"?

Please share some sample data if you don't have any Confidential Information.

Thanks,

Jay

[amitchandak]

Create a calendar table. there you can have you financial year.

Create one year behind measure which will show last year

last year Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))


Or use datesytd ot totalytd

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"3/31"))

Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"3/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"3/31"))

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/

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[edhans]

Can you show a sample of your data (in text or a spreadsheet shared via OneDrive, not a pic) and your goal? I am thinking you need to unpivot the columns for year 1/2/3. Years should not be in columns, but rows. Once you unpivot, everything will be a snap.

If you want to test this in Power Query yourself, go to the Item column and select it and all other informational columns. Then on the Transform tab, hit the Unpivot Columns dropdown button and select "Unpivot Other Columns"

Then this:

becomes this:

Now you can create a single column with years based on logic, rename the "Values" column to units and continue. The reason you want to use Unpivot Other columns vs selecting the years and Unpivot Columns is next year you will have Year5, and Unpivot Columns will not get that. But selecting Items and Unpivot Other Columns will correctly unpivot forever.