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

[4TCON]

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

 

[TomMartens]

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

[4TCON]

Hello .. I am uploading the .pbx file.   On the first tab, there is an explanation and a sample of what I need to produce.

Also on    tab:   6 months pH & EC  are 2x charts.   They are adding up the multiple years not displaying the months/years along the bottom axis.    How do I get the chart to display the months & years as they are selected in the slicers?

Thanks so much.

https://www.dropbox.com/s/d63k9avpd36y8zz/Enviro%20Dashboards%20-%20version%20for%20public.pbix?dl=0

[mede]

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]

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

[v-chuncz-msft]

@4TCON,

It would be better if you could simplify your model and share us the .pbix file.

[4TCON]

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.