To show data by specific period instead of FY

[yks4202]

Hi Everyone
I am newbie to Powerbi and need some advice.

We are a rental company and have customers who joined our membership in different period their membership is renewed yearly.
However, our sales data are in sorted FY.

https://file.io/4fPoUvDyLkLc

How can we show the % of sales data according to the customer membership period over the different FYs.

Thank you in advance.

[v-aatheeque]

Hi @yks4202 

Thanks for reaching out to the Microsoft Fabric Community forum.
We are unable to access the sample file which was shared by you, as it is protected and restricted by Microsoft's security policies.

1. I have created a sample dataset as follows, based on your description:

2.Next, I’ve developed a measure to calculate the percentage of sales data corresponding to customer membership periods across different financial years:

Sales Within Membership Period = 
CALCULATE(
    SUM('Sheet1'[SalesAmount]),
    'Sheet1'[TransactionDate] >= 'Sheet1'[MembershipStartDate],
    'Sheet1'[TransactionDate] <= 'Sheet1'[MembershipEndDate]
)
​

3.Here are the results generated using the above approach.

Below is the attached PBIX file related to the case for your reference.

If this post was helpful, please consider marking Accept as solution to assist other members in finding it more easily.

If you continue to face issues, feel free to reach out to us for further assistance!

[ryan_mayu · Accepted solution]

@yks4202 

pls see if this is what you want

[yks4202]

@ryan_mayu 
Yes, it is!😍

May I trouble you to help me with the percentage change between the FY22.

Just wondering if powerbi allow users to key in data?

For example: if nov 24 data is available, can the end user key in this value for all the customers or it has to been done via uploading excel.

[ryan_mayu]

@yks4202 

what do you mean by between FY22? how do you want to show the output? could you pls update the expected output?

I don't think we can key in the data to update the data. We can update the data in excel and refresh powerbi to update the data. If you have nov 24 in excel. The output will automatically update. You can have a try.

[yks4202]

@ryan_mayu 

Hi, for the percentage woud be something to show the changes when we compare this year sales to last year sale.

Sum of FY22 = 171

Sum of FY23 = 249 

% increase = (Sum of FY23- Sum of FY22) / Sum of FY22

Am I correct?

Thanks, will try to update the numbers for Nov in excel.

[ryan_mayu]

@yks4202 

i think you can create a date table and create a mesure

Measure =

VAR _last=CALCULATE(sum(Append1[Value]),DATEADD('date'[Date],-1,YEAR))

return if(ISBLANK(_last),blank(),DIVIDE(sum(Append1[Value]),_last)-1)

pls see the attachment below

[v-aatheeque]

Hi @yks4202 ,
we haven't heard back from you regarding our last response and wanted to check if your issue has been resolved.

If our response addressed your query, please mark it as Accept Answer and click Yes if you found it helpful.

Should you have any further questions, feel free to reach out.
Thank you for being a part of the Microsoft Fabric Community Forum!

[yks4202]

@ryan_mayu 

Hi, i couldnt change the data source in the your file, so i re create one with another set of data.
But somehow the total does not look right, but not sure where I have gone wrong. can you advice?

For example: 
Customer A membership is from April to March.
So using the same period:
FY24: Apr24 to Oct24 = 160 but chart show 270
FY23: Apr23 to Oct23 = 102 but chart show 234

I also tried to create a table to show CustomerA data for all the FYs but not sure how, is this possible?

Thank you so much for your help. 

Data file https://easyupload.io/jgzha1

pbix https://easyupload.io/h8rxc8

[ryan_mayu]

You need to set up the column to "y"

Previously, I add this to visual filter and set to y, you can add a new slicer and set to y as well.

[yks4202]

Thank you so much for the advice. It works!

[ryan_mayu]

you are welcome

[yks4202]

@ryan_mayu 

Hi, here are the data files and expected results. Hope the links works.

Currently we are using excel to manually calcluate the usage, hence we are exploring if powerbi is able to make things easier, but we got lost.

Data file: https://easyupload.io/in30mh
Expected result: https://easyupload.io/s5qts5

Thanks so much for looking at it.

[ryan_mayu]

can't download the sample data.

Btw, what's the expected output?