Need help with simple date calculation in Power BI

[powerbignc]

I have a simple model where I have a fact table with Transaction ID and Transaction Date. I also have a dates table where I have three columns: [Date], [DateLastYear], and [DateOneYearAgo]. I would like to be able to create three measures where I can look at transaction counts as of [Date] (**Easy**) and also transaction count as of [DateoneYearAgo], and [DateLastYear]. So I know for example when I look at the "1/1/2014" date, I see a count for that day, but I also see under 1/1/2014 what the transaction count was for [DateLastYear] and [DateOneYearAgo]..... then from here i can start to build additional measures for YoY calcs. Like YoY = [Count]-[DateLastYearCount]

Here is a preview of the outcome needed...How do I build the last two measures, using what I have now in the dates table?

***Also how woud I setup my model given that my fact table has one date field and my date table has three different columns for dates...??***

Currently I have it set up as a 1:Many going from [Date] > [Transaction Date] filter direction is from [Date] to Transaction Table... but what about the other two date fields?

For example three measure are to count TRX amount, and the amounts from LastYear and OneYearAgo.

Count:= Count([Transaction ID])

DateLastYearCount:= ???

DateOneYearAgoCount:= ???

As you can see DateLastYear and DateOneYearAgo might be off a day or so but that is how we need to report.. We already did a lot of work building this date table, now just need to figure out how how to build the other measures and do the YoY calculations....

Date Table

Transaction Fact Table

Any help would this would be much appreciated!!

Thanks!

[v-yanjiang-msft · Accepted solution]

Hi @powerbignc ,

I download the Excel you provided, but there's only data in the year 2014 in Transaction table, so I create a new sample.

Transaction table:

Date table:

Here's my solution, create three measures:

Count =
COUNTROWS (
    FILTER (
        ALL ( 'Transaction' ),
        'Transaction'[Transaction Date] = MAX ( 'Date'[Date] )
    )
)

DateLastYearCount =
COUNTROWS (
    FILTER (
        ALL ( 'Transaction' ),
        'Transaction'[Transaction Date] = MAX ( 'Date'[DateLastYear] )
    )
)

DateOneYearAgoCount =
COUNTROWS (
    FILTER (
        ALL ( 'Transaction' ),
        'Transaction'[Transaction Date] = MAX ( 'Date'[DateOneYearAgo] )
    )
)

Put the Date column of the Date table in a Matrix Columns, put the three measures in Matrix Values and turn on the "Switch values to rows option" in the visual formatting pane>Values>Options. Get the correct result.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

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

[claymcooper]

One thing I've done in several cases is have a "Date" Table and a "Date Equivalent" Table. In the Date table I have multiple years of data containing information such Day of Week, Month Name, Week Number etc. In the Date Equivalent Table, I have the same information but only for the current year. I then merge the Date Equivalent Table onto the Data Table joining on the Day Number of Year. This lines up all the dates in the Date table with the equivalanet date in the current year. From there, I just use the Date Equivalent field in all my visuals and the calculations will line up for each year in the Date Table.

That doesn't neccessarily help you based on your current table structure but may be something you could try if you can make that work.

Another option would be to try CALCULATE(COUNT([Transaction ID]), Date = DateLastYear)

[powerbignc]

I am not sure that would work since my actual fact table is millions of rows tall and I also have multiple fact tables plugging into that date table. Some are financial tables, Sales Data Tables, HR Tables, etc...I was thinking of the USERELATIONSHIP feature in DAX... See here. https://www.sqlbi.com/articles/using-userelationship-in-dax/

Would this work?

[indkitty]

Hi Powerbignic,

It works for 

DateLastYearCount as the values are unique, but for DateOneYearAgoCount the dates are duplicated. When I imported your date table, the format did not appear correctly for me. I'll take a look later.

[powerbignc]

I noticed that too for DateOneYearAgo... This only happens because those are Leap Year Days. 2/29/2016 was a leap day, but they decided to make the "DateOneYearAgo" to be 2/28/2015 since there is no 29th.... However this messes up 2/28/2016 since the "DateOneYearAgo" is also 2/28/2015.... Thoughts on this?

[claymcooper]

The merge wouldn't happen on any of the fact tables, just on the date table, which shoul only have 1 row per day. and the merge wouldn't add any new rows. The relationship would be between the date in your date table and the date in your fact table(s) but you would use the date equivalant column the date table for all your visuals and filters.

[powerbignc]

Not sure I follow. I already have a merged Dates table with [Dates] and equivilant prior year dates. I just need last years values to align with this years values so I can do Variance and other YoY comparisions... Here is my dates table....My fact table has a dates column which is joined to the [Date] column of the dates table... How do I get all my fact data to align? For example if I have a transaction on 1/1/2014 I see that value when I pull in date, but what about the value for 1/1/2013 that would align to the 1/1/2014 date?

Date	DateLastYear	DateOneYearAgo
1/1/2014	1/2/2013	1/1/2013
1/2/2014	1/3/2013	1/2/2013
1/3/2014	1/4/2013	1/3/2013
1/4/2014	1/5/2013	1/4/2013
1/5/2014	1/6/2013	1/5/2013
1/6/2014	1/7/2013	1/6/2013
1/7/2014	1/8/2013	1/7/2013
1/8/2014	1/9/2013	1/8/2013
....etc for the rest of the 365 days....	...	...
...	...	......
...	...	...
...	...	...
...	...	...
...	...	...

[indkitty]

Hi Powerbignc,

if possible could you share these two tables for January period?

[powerbignc]

Sure! Here is the Excel with the data tables as a sample. Complete Date and Transaction table.

Here is a link to the Excel file via OneDrive.
https://1drv.ms/x/s!AmSfpgax5s9xslK_njkNxQT6_vUY?e=uCNQYe

Let me know if you have any questions.

Thanks,

[indkitty]

Hi Powerbignc,

I think I figured out.

As there was no data for last year. I had to create dummy data for last year, last one year from the same sample with change in date. 

Do you want the data to be in the same format. i.e Dates as columns, values in Rows. I'll convert this into Power Bi.

[powerbignc]

Yes the dates you have across the column would come from the [Dates] Column of the DatesTable, and yes the values (Measures) would be dragged into Rows like you have above. So NOW you can create additional measures to show variances (if you wanted) like Count-Count.1 in the example above and see "-1" for the month of 1/1/2014...

[indkitty]

Hi Powerbignc,

Below would be the output. 

Considerations:- Though we can achieve the result, we should use different method to differnt method.

Refer to below article.

Previous year up to a certain date - SQLBI

For the below method to work.

1. DateTable need to normalise the table. 

2. Measurement to be created in additonal table.

3. Both table to be joined.

4. Another Datetable is created with an index.

Hope this solves your issue.