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.
Hoping someone can help! Each month I receive a finance file, the total number of rows varies each month. The two columns of interest are financial_ref (which is a unique identifier for each month, though repeated across months) and outcome (which is either Yes or No). A 100% stacked bar chart can show the yes/no% each month (axis = date, value = count(financial_ref), legend = outcome), but what I'd also like is a chart which can show the % difference each month. So if 'No' was 48% in March and 50% in April, I'd like a chart which shows +2% for 'No' for April, being the increase from the previous month (and likewise shows -2% for 'Yes' for April). There's an additional column 'Category' which stakeholders would use to filter any visualisations built. No idea where to start...
Solved! Go to Solution.
Hi @Anonymous ,
assuming you have a Date table you can try these measures:
Total Outcome = CALCULATE(
COUNTA(Sheet1[Outcome]))
Total Outcome Prev Month = CALCULATE(
[Total Outcome],DATEADD('Date'[Date],-1,MONTH))
% Monthly Change = DIVIDE(
[Total Outcome] - [Total Outcome Prev Month],[Total Outcome Prev Month], 0)
Hope it helps.
Cheers,
Marco
Hi @Anonymous ,
check this out:
https://drive.google.com/file/d/1WD35GYlk-SWcSC-JRz8Gywbiyg1U_-Op/view?usp=sharing
Cheers,
Marco
@Anonymous , you can use time intelligence to get current and last month and then take diff of that
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(Table[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd(Table[Date],-1,MONTH)))
last MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd(Table[Date],-1,MONTH))))
last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd(Table[Date],-12,MONTH)))
last year MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd(Table[Date],-12,MONTH))))
Month behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd(Table[Date],-1,Month))
last QTR same Month (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd(Table[Date],-1,Qtr))))
MTD (Year End) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR(Table[Date])))
MTD (Last Year End) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR(dateadd(Table[Date],-12,MONTH),"8/31")))
Hi @Anonymous ,
assuming you have a Date table you can try these measures:
Total Outcome = CALCULATE(
COUNTA(Sheet1[Outcome]))
Total Outcome Prev Month = CALCULATE(
[Total Outcome],DATEADD('Date'[Date],-1,MONTH))
% Monthly Change = DIVIDE(
[Total Outcome] - [Total Outcome Prev Month],[Total Outcome Prev Month], 0)
Hope it helps.
Cheers,
Marco
Hi @MarcoPessina ,
that table is exactly what I'd like, but after trying the measures it wasnt giving me right figures. I created a new small table to test it on too, as below,
Reference | Date | Value |
a | 01/03/2020 | Yes |
b | 01/03/2020 | No |
c | 01/03/2020 | No |
d | 01/03/2020 | Yes |
e | 01/03/2020 | No |
a | 01/04/2020 | Yes |
b | 01/04/2020 | No |
c | 01/04/2020 | Yes |
d | 01/04/2020 | No |
e | 01/04/2020 | Yes |
f | 01/04/2020 | No |
Getting this result though??
Not sure what I'm missing? Many thanks for your help!
Hi @Anonymous ,
check this out:
https://drive.google.com/file/d/1WD35GYlk-SWcSC-JRz8Gywbiyg1U_-Op/view?usp=sharing
Cheers,
Marco
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 |
---|---|
97 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |