Forum Discussion

powerbignc's avatar
powerbignc
Icon for Helper I rankHelper I
4 years ago
Solved

Need help with simple date calculation in Power BI

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!

 

Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    4 years ago

    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.

12 Replies

  • indkitty's avatar
    indkitty
    Icon for Helper II rankHelper II
    4 years ago

    Hi Powerbignc,

     

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

      • indkitty's avatar
        indkitty
        Icon for Helper II rankHelper II
        4 years ago

        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.

         

         

         

  • claymcooper's avatar
    claymcooper
    Icon for Resolver II rankResolver II
    4 years ago

    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's avatar
      powerbignc
      Icon for Helper I rankHelper I
      4 years ago

      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?

      • claymcooper's avatar
        claymcooper
        Icon for Resolver II rankResolver II
        4 years ago

        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.

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    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.