Forum Discussion
End user to select start & end date
Hi Everyone,
Need some advice. I have a file which contain sales data extracted from system in fiscial year.
the object is to allow end use to query the sale data and compare the same period of sale. I am not sure if this can be achieve?
1) End user enter MM, YY & Product Name
It will display the sale data in a table format below
2) Next, It will calculate the same period last year and the % increase for FY24 vs FY23 and so on
Eg Same period last year would be
Jan 2024 - Jun2024 = 322 (% change = X)
Jan 2023 - Jun2023 = 356 (% change = X)
Jan 2022 - Jun2022 = 331
Appreciate your advice. I have attached the files.
https://1drv.ms/u/s!Al8hd8f9ED8EbXeWx-c8j5YD43U?e=gKzTlx (pbix)
https://1drv.ms/x/s!Al8hd8f9ED8Ebs0SDcepNBkdGoE?e=P4IhmR (data excel)
Thanks for looking at it.
Hi lilskyy4202,
Thank you for your valuable feedback.
In response to your query, please find attached the screenshot and the PBIX file for your reference.
If you find this response helpful, kindly mark it as the accepted solution and provide kudos, as this will assist other members with similar queries.
Best regards,
Pavan
8 Replies
v-pnaroju-msftCommunity Support
Hi lilskyy4202,
We value your inquiry through the Microsoft Fabric Community Forum.
In addition to the response provided by tharunkumarRTK, please find the attached PBIX file to address the query.
Kindly refer to the steps below to calculate the percentage change of -95.42%:
- Sales LY = 131 (For Product 1 in January 2023) → LY stands for Last Year.
- Total Sales = 6 (For Product 1 in January 2024).
The formula to calculate % Change is as follows:
% Change = DIVIDE([Total Sales] - [Sales LY], [Sales LY]) DIVIDE(6-131/131) DIVIDE(-125/131) => -0.95419 Convert into percentage(%) by multiplying with 100 =>(-0.95419 * 100) => -95.419 => -95.42%(approx)Thus, the percentage change is approximately -95.42%.
If you find the response helpful, kindly mark it as the accepted solution and provide kudos. This will assist other community members facing similar queries.
Best Regards,
Pavanv-pnaroju-msft Thanks Sir...
Yes it is what i am looking for.
How do I get the graph to show the same period for the year before Last year also?
FY24: Jan 2024 - Jun2024 = 322 (% change compared to FY23 = 9.55)
FY23: Jan 2023 - Jun2023 = 356 (% change compared to FY22 = 7.5)
FY22: Jan 2022 - Jun2022 = 331
Thank so much for your advice.
- v-pnaroju-msft
Hi lilskyy4202,
Thank you for your valuable feedback.
In response to your query, please find attached the screenshot and the PBIX file for your reference.
If you find this response helpful, kindly mark it as the accepted solution and provide kudos, as this will assist other members with similar queries.
Best regards,
Pavan
- tharunkumarRTK
Super User
Many thanks for the file.
would you advice me how to get -95.42% in change in sale.
I am a bit lost here.
For Jan-Jun
2024 Current year of sale = 322
2023 Last year sale = 356Thank you so much for helping.
- Ashish_Mathur
Hi,
If the time span will always remain 6 months, then why are we giving the choice of selecting both beginning and ending months to the end user? We should ideally allow the user to select only the ending month and then via a measure total the sales for the 6 months ended that month.
Please let me know if you agree with my approach.
Ashish_Mathur Thank you, sir.
Yes , we thought of using a 6 month period, however we have scenario when the management want to enquire the period when there is a sale promotion and compare it with last year sales. The promotion could range from 3-6 months.So we thought it would be for them to query the date period.
Currently it was done manually whenever there is request for the data.- Ashish_Mathur
Hi,
PBI file attached.
Hope this helps.
