Forum Discussion

4TCON's avatar
4TCON
Frequent Visitor
9 years ago

calculate difference in value between dates, using year and month slicers

Hi,  I am new to Power Bi.  

 

I need to create a graph that shows the difference in a number between two date periods.  The number is a water level measurement and we need to allow the client to select a year and a month, and for the graph to then show any difference in the two date measurements.

 

I have put the year and months into separate columns in the data source excel file.     Is this the best way to do this or should they be in one colum (month / year)?

 

Basically there are 4x columns: Site, Year, Month, SWL value (the water level value). 

 

How do I create a measure that will look at the SWL value for a month and year and then compare any difference to the previous period?    I am expecting the graph to end up with a range of -5 through to 5 as the water levels rise and fall over the period. 

 

I have attached a sample chart from the excel file that we currently use to show what I am aiming for.

 

Thanks,   Kelle

 

 

 

 

6 Replies

  • 4TCON's avatar
    4TCON
    Frequent Visitor
    9 years ago

    Also,  I need conditional colouring so if a comparison measure goes below -2, it will change colour to orange  (like in the sample chart).    Thanks.

  • mede's avatar
    mede
    Resolver I
    8 years ago

    A PBIX file would be helpful for us to understand better. But my understanding so far is that you want to see the SWL in the SAMEPERIODLASTYEAR as well as the Change between Current Year and Prior Year.

     

    Option 1:

    CALCULATE and DAX time intelligent functions can help you with this, but in order to use them you need a calculated column in date format (so just Year and Month will not be sufficient, you need to use the Year and Month to create a date column, you may use a dummy day value for example 1st of each month) For example:

    Date = DATE('Dim Date'[Year],'Dim Date'[Month],1)

    Then,

    your SWL Previous year measure will need something like this:

    SWL Previous Year = CALCULATE(SUM(SWL), SAMEPERIODLASTYEAR(Date))

    check this for other types: https://msdn.microsoft.com/en-us/library/ee634763.aspx

     

    Option 2:

    You can do the same without creating a date column again using the magical CALCULATE.

    For example in order to create a measure called [SWL Previous Year]:

     

    SWL Previous Year = CALCULATE(SUM(SWL), Year = VALUES(Year)-1)

    Year has to be in Whole Number format.

     

    keep in mind this approach is not optimal and will fail for Totals and Subtotals. That's why  Time Intelligent functions are more advisable.

     

    Finally, you can calculate your change by creating a measure like:

    SWL Change = SUM(SWL)- SWL Previous Year
    • 4TCON's avatar
      4TCON
      Frequent Visitor
      8 years ago

      Thank You!  I will try these suggestions.  It all is a bit foreign to me still, however I will give it a go.   :smileyvery-happy:

  • TomMartens's avatar
    TomMartens
    Super User
    8 years ago

    Hey,

     

    1st things first: welcome to this community

     

    2nd: Trying to provide an answer to your question ...

     

    My answer to your question starts with: Create a separate Calendar table by using the DAX function CALENDAR(<startdate>, <enddate>)

    Create a calculated column in your existing table of datatype date/datetime by using this DAX formula:

    DATE('table'[yearcolumn], 'table'[monthcolumn], 1)

    Then, relate both tables with the calendar table on the one side and your table on the many side.
    Using a separate Calendar/Date table is a data modeling best practice in Power BI. This will make calculations much more simple if you have to consider values from different years, that most probably will happen, if you visualize something covering a timeframe of the last 12 months. Here you will find further extensive examples of time related calculations in DAX: daxpatterns.com/time-patterns

     

    Then you create a measure like this

    CALCULATE(SUM('yourtable'[SWL mBTOC]) - 
CALCULATE(SUM('yourtable'[SWL mBTOC]), PREVIOUSMONTH('theCalendarTable'[theDateColumn])

    Please be aware that you have to use the columns from the Calendar table within your visuals instead of the columns of your existing table.

     

    Regards

    Tom