Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all,
Would someone be able to help with a problem I need solving?
I need to take the count from the 1st period of each fiscal month and apply in a column next to the proceeding months within that fiscal year.
The fiscal year starts in Apr, so I need something like looks the below
Fiscal Year | Count | CountofFirstFiscalMonth
Apr 2017 | 7 | 7
May 2017 | 5 | 7
May 2017 | 9 | 7
I've enclosed a sample data I'm working using
Tab 1 - FactData Sample
Tab 2- DimDate table Sample
Tab 3 - an example of what I'm trying to Achieve.
Sample Data =>
https://drive.google.com/file/d/10O0MDXWoBK39xFAFADh23MJ8v8_7m1EL/view?usp=sharing
Thanks indvance for assistance.
Solved! Go to Solution.
hi, @Anonymous
You could try this formula to add a calculate column
new result = CALCULATE(MAX(Table1[Month Count]),FILTER(ALLEXCEPT(Table1,Table1[FiscalYear]),Table1[FiscalMonth]=1))
Best Regards,
Lin
hi, @Anonymous
You could try to use EARLIER Function to add a column like below:
Result = CALCULATE(MAX(Table1[Month Count]),FILTER(ALL(Table1),Table1[FiscalYear]=EARLIER(Table1[FiscalYear])&&Table1[FiscalMonth]=1))
Result:
Best Regards,
Lin
Thanks for this.
How would I amend the formula when using only the first two tabs of the demo to create the data model? Ideally I would like to add a Calculated Column to the factdata table to create an output similar the below but with the extra result column containing first month of the fiscal year.
Thanks again for your help.
hi, @Anonymous
You could try this formula to add a calculate column
new result = CALCULATE(MAX(Table1[Month Count]),FILTER(ALLEXCEPT(Table1,Table1[FiscalYear]),Table1[FiscalMonth]=1))
Best Regards,
Lin
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
98 | |
78 | |
65 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |