Merging columns with observation summing last 12 months

[ChristianRHouen]

Hi

I would like to solve below:

Merging the tables is not a problem, but doing it in the power editor will just sum all observations by the unit IDs. I would like it to be dynamic, so I in my output table can see how many observations have been made in the last 12 months (or different period).

[v-yuta-msft]

@amitchandak ,

Merging the tables is not a problem, but doing it in the power editor will just sum all observations by the unit IDs. I would like it to be dynamic, so I in my output table can see how many observations have been made in the last 12 months (or different period).

---

Could you please clarify more details about your requirement?

Regards

Jimmy Tao

[ChristianRHouen]

@v-yuta-msft 

Hi,

I attached a picture of what I want - could you specify your question, please?

[amitchandak]

You should create a table having a unique Unit_ID.

Join that with these to the table.  It needs to have a bidirectional relation with the Active_unit table.

I do not see any date. But have date table joined with time.

Post that you should be able to create the required table.

[ChristianRHouen]

@amitchandak @v-yuta-msft 

The issue here is that we have two dates.

In my dashboard I would like to be able to select a given month (active_date), and see all the IDs for this month in first colum. In the second column I would like to see the number of observations made in the last 12 months.

E.g if we pretend Active_date: 1 = January 2019 and I select this value, I want to see dongle 1,2,3 and 4 in the first column. In the second column I want to see all Observations (observations column) made in (obs_date) month 0 (December 2018) to month -12 (january 2018.)

I hope this makes sense 🙂

[amitchandak]

Assume Date[Date filter] is the date selected. These are the few calc you can refer to, using dates or month diff.

On the Fly Change % = 
Var _last_year= (max('Date'[Date Filer]))-365
Var   _This_year=year(max('Date'[Date Filer]))
Var _min_last_year= (maxx('Date',STARTOFYEAR(DATEADD('Date'[Date Filer],-12,MONTH))))
Var   _min_This_year=year(max(STARTOFYEAR('Date'[Date Filer])))



Var _last_year= (maxx('Date',ENDOFYEAR(DATEADD('Date'[Date Filer],-12,MONTH))))
Var   _This_year=year(max(ENDOFYEAR('Date'[Date Filer])))
Var _min_last_year= (maxx('Date',STARTOFYEAR(DATEADD('Date'[Date Filer],-12,MONTH))))
Var   _min_This_year=year(max(STARTOFYEAR('Date'[Date Filer])))


Var _last_year_val= CALCULATE(sum(Sales[Sales Amount]),(Sales[Sales Date])<=_last_year && (Sales[Sales Date]) >=_min_last_year)
Var   _This_year_val =CALCULATE(sum(Sales[Sales Amount]),(Sales[Sales Date])<=_This_year && (Sales[Sales Date]) >=_min_This_year)



Sales YTD = 
Var   _start_date=year(max('Date'[Date Filer]))
Var   _end_date=year(min(STARTOFYEAR('Date'[Date Filer])))

Var _last_year_mtd_val= CALCULATE(sum(Sales[Sales Amount]),Sales[Sales Date] >= _start_date && (Sales[Sales Date]) <= _end_date, filter(Sales,CALCULATE(sum(Sales[Sales Amount]),SAMEPERIODLASTYEAR(Sales[Sales Date]))>0))
return
_last_year_mtd_val