Forum Discussion
Helper IPowerbi: Need column difference based on slicer selection by user
I am new to Power bi. I have tables -> calender , bi_alloceffort_data & bi_alloceffort_data_main. I Have linked calender date column to REVDATE column of bi_alloceffort_data & "PROJECTNO" of both the tables.
I have created a timeline slicer from the calender table.
I have two queries:
- In slicer visual -> Format-> fiscal year, I selected April as the fiscal year. But the slicer shows 20-Oct-2019 as 2020 Q3 instead of 2019 Q3. Need to get it correct.
2.When user select a date range from the slicer, I need "ST_CLGBAL" -"ST_PREVCLGBAL" (from table bi_alloceffort_data) for the "JOBNO" where "REVDATE" lies in the user selected date range from the slicer.
pbix saved at below link .
Hi ankurbajaj07,
Can you update below DAX query and try one?
difference =
CALCULATE (
SUM ( BI_ALLOCEFFORT_DATA[ST_CLGBAL] )
- SUM ( BI_ALLOCEFFORT_DATA[ST_PREVCLGBAL] ),
KEEPFILTERS ( BI_ALLOCEFFORT_DATA[REVDATE] )
)Thanks,
Prashanth
8 Replies
- danextian
Super User
ankurbajaj07Pbix uploaded.
- danextian
Hi ankurbajaj07
Query 1: The slicer is showing correct dates. Why would it go back to 2019 whe you've selected FY20? The dates selected match the fiscal quarter and year in your calendar?
Query2: You can create a measure that calculated the difference between the sum of the two columns:
difference = SUM ( BI_ALLOCEFFORT_DATA[ST_CLGBAL] ) - SUM ( BI_ALLOCEFFORT_DATA[ST_PREVCLGBAL] )- ankurbajaj07
Hi , thanks for the reply.
Query 2: this measure is summing up ST_CLGBAL for all the instances of job (Rev0 , Rev1...) once the job is filtered based on REVDATE lying within slicer range. i.e. it is clearing the slicer filter for summing up ST_CLGBAL once the job is selected based on slicer range. However, I need ST_CLGBAL for only that job instances which lies within the slicer range. i.e. ST_CLGBAL should be from the selected slicer range only corresponding to the job which is filtered from slicer range.
- v-prasare
Community Support
Hi ankurbajaj07,
Can you update below DAX query and try one?
difference =
CALCULATE (
SUM ( BI_ALLOCEFFORT_DATA[ST_CLGBAL] )
- SUM ( BI_ALLOCEFFORT_DATA[ST_PREVCLGBAL] ),
KEEPFILTERS ( BI_ALLOCEFFORT_DATA[REVDATE] )
)Thanks,
Prashanth
- v-prasare
Hi ankurbajaj07, As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for your issue worked? or let us know if you need any further assistance here?
Thanks,
Prashanth Are
MS Fabric community support
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and give Kudos if helped you resolve your query
- v-prasare
Hi ankurbajaj07, As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for your issue worked? or let us know if you need any further assistance here?
Thanks,
Prashanth Are
MS Fabric community support
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and give Kudos if helped you resolve your query
- v-prasare
Hi ankurbajaj07,
As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for your issue worked? or let us know if you need any further assistance here?
Thanks,
Prashanth Are
MS Fabric community support
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and give Kudos if helped you resolve your query
