Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ChristianRHouen
Helper III
Helper III

Merging columns with observation summing last 12 months

Hi

 

I would like to solve below:

Capture.JPG

 

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).

5 REPLIES 5
v-yuta-msft
Community Support
Community Support

@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

@v-yuta-msft 

Hi,

 

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

amitchandak
Super User
Super User

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.

 

@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 🙂

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

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.