Forum Discussion

yaman123's avatar
yaman123
Post Partisan
5 years ago

Full Financial Year Volume

Hi all, 

 

A bit of a tricky one. 

 

I have a dataset with Customer Code, Name, Sample Date, Volume, Supply End Date and Termination Reason. 

 

Sample date is the date when the volume was collected. 

 

Supply End Date is if the customer has left the business. 

 

I need a measure which will give me a Financial YTD Volume figure for anyone left the business. The FY is from April-March. 

 

Examples: 

If i select Supply End Date = Feb 2021 

3 people left the business and i would like to calculate the Full FYTD volume figure from  Apr 19 - Mar 20. This will need to be a full financial year and not a partially completed financial year. 

 

If i select Supply End Date = Apr 2021

I need the FYTD Volume from Apr 20 - Mar 21 etc.. 

 

Hope this makes sense

 

TIA

 

 

 

 

 

 

 

Need Help
Show and Tell
Tips and Tricks

9 Replies

    • yaman123's avatar
      yaman123
      Post Partisan
      5 years ago

      Hi, 

       

      I have a date table and the date column links to the sample date column in my dataset. 

      • aj1973's avatar
        aj1973
        Community Champion
        5 years ago

        Good,

        add another inactive relationship from Calendar to Supply End Date.

        Then use TOTALYTD function and USERELATIONSHIP to call for Supply End Date.

         

        It might not be clear for you, but it is for me as well. How about you share a sample of your file?

         

  • v-henryk-mstf's avatar
    v-henryk-mstf
    Community Support
    5 years ago

    Hi yaman123 ,

     

    I checked the pbix file provided by aj1973. Itshould be able to help you. If the problem persists, please provide test data (delete sensitive information), and I will answer you as soon as possible.


    Looking forward to your reply.

    Best Regards,
    Henry

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

    • yaman123's avatar
      yaman123
      Post Partisan
      5 years ago

      Hi v-henryk-mstf 

       

      Here is the link to the dataset 

       

      https://drive.google.com/file/d/1Q2J15gDRwuUZQIMXeBnOyjJrsOGsCc-H/view?usp=sharing

       

      If you add supply end date as a slicer and select 01/02/2021 - 28/02/2021, it should show 3 members - 2110191, 2204188, 2501424. 

       

      These 3 members left in Feb 2021, so i would like to calculate the collection litres from April 2019 - March 2020 using the sample date as this is when the collection occured. 

       

      So total from April 2019 - March 2020 should be 2,301,199.

       

      Hope this makes sense