Change over period of time with respect to specific column

[aileenkaz]

Hello, 

I am new to PBI and just starting to learn to work with it.

I have got data from my colleague which I should put into dashboard. Only simple graphs were needed so I was quite successful until now. The request is to show the change of overall rating between two consecutive periods of time together with corresponding upwards/downwards arrow.

I have managed to learn how to calculate difference of overall between two consecutive rows. However, there is one common table for three types of period (month, quarter, year) and therefore I did not get correct values. During the calculation I need somehow to take into consideration also column type_of_period (e.g. I should compare value of Q2 2018 with jun 2018, but with Q1 2018). Can you help me?

I was thinking also about changing the data and using proper calendar function, but I am not sure what to do with values of quarters and years there, because they are not calculated from monthly values, but inserted manually.

I will by thankful for any ideas or requirements of literature/website/knowledge.

My data:

data

[v-alq-msft]

Hi, @aileenkaz 

Typically, your data model will contain a calendar table. It is usually better to aggregate data
by year and month using columns of a calendar table (containing one row for each day) instead
of extracting the date parts from a single column of type date or datetime in calculated columns.
There are a few reasons for this choice. You obtain a model wherein all date attributes are included
in a separate table making it easier to browse data using a generic client, and you can use special
DAX functions that perform time intelligence calculations. Moreover, most of the time intelligence
functions require a separate Date table to work correctly.

Here is my sample data for test.

Table:

You may create a calculated table as follows and then join it with 'Table'.

DateTable = CALENDARAUTO()

Then you can create measures to calculate the percentage of current month to current quarter.

current month = 
CALCULATE(
        SUM('Table'[Sales]),
        FILTER(
            ALLSELECTED('Table'),
            MONTH('Table'[Date]) = MONTH(MAX('Table'[Date]))
        )
)
current quarter = 
CALCULATE(
        SUM('Table'[Sales]),
        FILTER(
            ALLSELECTED('Table'),
            QUARTER('Table'[Date]) = QUARTER(MAX('Table'[Date]))
        )
)

Qt % = 
DIVIDE(
    [current month],
    [current quarter]
)

Result:

Best Regards

Allan

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

[aileenkaz]

@v-alq-msft: Hi Allan!

That's me again 😅 Here is a screenshot of my calendar and data. I don't know how to connect them.

If I had only one type of period (month), there would be a relationship between period and Month year and everything would work great. However, in some rows the value of period corresponds to the quarter (or year) instead of month, therefore I cannot do that in this way.

Probably the best thing to do is to create the data with a completely different structure?

calendar and data

[v-alq-msft]

Hi, @aileenkaz 

I'd like to suggest you create a date column in your fact table. The column 'period' has values corresponding to Month-Year or Querter-Year. It is unable to connect with that column.

Best Regards

Allan

[aileenkaz]

Hi, @v-alq-msft,

thanks for your answer.

My problem is I am not sure how to implement calendar into my data: I know that my monthly values will correspond with calendar months. However, I have separate values for each Quarter (which is not calculated out of months, but inserted).  What will be the relationship between value for specific quarter and the calendar table?

I´m not sure if I have explained it correctly. As soon as I will have the calendar implemented, I will post a screenshot here for you to help me understand better the connections between my data table and calendar table.

[amitchandak]

If you have date table, this how you can create various period measures

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
last year MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))
last year MTD Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))


last QTR same Month (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,Qtr))))


MTD (Year End) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR('Date'[Date])))
MTD (Last Year End) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR(dateadd('Date'[Date],-12,MONTH),"8/31")))

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-12,MONTH)) 
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date Filer],MAX(Sales[Sales Date]),-12,MONTH))  
3 month back MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-3,MONTH)))

 


QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))

Last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,QUARTER)))
Last to last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-2,QUARTER)))
Next QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],1,QUARTER)))

Last year same QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,Year)))

How to compare rank refer. There is an example of this week vs last week

https://www.dropbox.com/s/0ec5u2nhxuo22eb/weeklysellerRank.pbix?dl=0

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/