Forum Discussion

jherbert1017's avatar
jherbert1017
New Member
6 years ago
Solved

comp date measure

New to Dax and trying to figure this out,  I have two tables as below.  Trying to calculate a measure that will sum the income based on the comp date when the slicer on the page is set to the visit d...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi jherbert1017 

    I build two table like yours to have a test.

    Transaction table:

    You don’t need to merge Transaction table and Calendar Table. And if you feel your report is slow and performance is not good, you may need to optimize performance. For more info about optimize performance:Optimization guide for Power BI

    You may achieve your goal by measure.

    Firstly we use Visit Date in Calendar Table to build a Slicer.

    Then we build a measure as below:

     

     

    TotalIncome = 
CALCULATE(SUM('Transaction'[Income]),FILTER(ALL('Transaction'),'Transaction'[Visit Date]=MAX('Calendar'[Visit Date])))

     

     

    Add this measure in a card visual or Transaction table visual to show the result.

    Result:

    Select 2019/7/4 in slicer

    You can download the pbix file from this link: Comp date measure

     

    Best Regards,

    Rico Zhou

     

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

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

jherbert1017 , Have a date table, join it with visit date active, and comp date inactive.

refer this blog How to work with inactive join using userelation

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in-power-bi

 

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/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


Appreciate your Kudos.