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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
lw24
Helper I
Helper I

Selected Filter Date / Column when appending data reports

Hi All

 

I'm currently building a bi weekly report with data pulled directly from Salesforce that's wrangled in a different system and I'd like to give the viewers an inside look into performance metrics for a given report pull date (ie 7/31 vs 8/31). I have added example data but I have a couple dax measures using Calculate, distinct, sumx, countx for example. Posted below... 

 

I will be appending the data in one report and am wondering how I can give my viewers the ability to look at the data based on the pulled date of the report (ie. a slicer to choose between 7/31 vs 8/31). 

 

Thanks!!!

 

lw24_0-1690829544742.png

 

What I'd like to answer: 

 

1. Calculate sum and avg amount by contact as of [date]

2. Count the number of instances a count had a win as of [date] (dax below)

 
Dax Example:
 
CALCULATE(DISTINCTCOUNT('Opportunity Detail'[Contact ID (18)]), 'Opportunity Detail'[Stage (Bucketed)] = "Won")
1 ACCEPTED SOLUTION
v-cgao-msft
Community Support
Community Support

Hi @lw24 ,

1. In Power BI, create a new table with the distinct dates from your data.

Dates = DISTINCT('Table'[Date])

Add a slicer to your report and use the new table as the source.

vcgaomsft_0-1693296048373.png

2. For the first question, create two measures to calculate the sum and average amount by contact as of the selected date:

SumAmountByContact = 
   CALCULATE(
       SUM('Table'[Amount]),
       'Table'[Date] <= SELECTEDVALUE('Dates'[Date])
   )
AvgAmountByContact = 
   CALCULATE(
       AVERAGE('Table'[Amount]),
       'Table'[Date] <= SELECTEDVALUE('Dates'[Date])
   )


3. For the second question, create a measure to count the number of instances a contact had a win as of the selected date:

CountWinsByContact = 
CALCULATE(
    DISTINCTCOUNT('YourTableName'[Contact ID]),
    'YourTableName'[Stage (Bucketed)] = "Won",
    'YourTableName'[Date] <= SELECTEDVALUE(Dates[Date])
)

 

Best Regards,
Gao

Community Support Team

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

View solution in original post

1 REPLY 1
v-cgao-msft
Community Support
Community Support

Hi @lw24 ,

1. In Power BI, create a new table with the distinct dates from your data.

Dates = DISTINCT('Table'[Date])

Add a slicer to your report and use the new table as the source.

vcgaomsft_0-1693296048373.png

2. For the first question, create two measures to calculate the sum and average amount by contact as of the selected date:

SumAmountByContact = 
   CALCULATE(
       SUM('Table'[Amount]),
       'Table'[Date] <= SELECTEDVALUE('Dates'[Date])
   )
AvgAmountByContact = 
   CALCULATE(
       AVERAGE('Table'[Amount]),
       'Table'[Date] <= SELECTEDVALUE('Dates'[Date])
   )


3. For the second question, create a measure to count the number of instances a contact had a win as of the selected date:

CountWinsByContact = 
CALCULATE(
    DISTINCTCOUNT('YourTableName'[Contact ID]),
    'YourTableName'[Stage (Bucketed)] = "Won",
    'YourTableName'[Date] <= SELECTEDVALUE(Dates[Date])
)

 

Best Regards,
Gao

Community Support Team

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.