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.
Dear Experts,
I have 2 data files for month Mar and Apr. I want to create a matrix visual wherein I should be able to calculate the difference between 2 columns in the matrix visual
Here is how the data looks like for both Months and even the output expected:
Mar -20
Apr -20
Output Expected
In PowerBI, I did append both Mar and April data and created a matrix visual with 'Business' in rows and 'Months' in columns and 'Sales Units' and 'Sales Value' in the Values. Now I want to calculate below:
Furthermore,
Is there a better solution available for above problem than the Matrix Visual?
I have attached the sample file.https://drive.google.com/file/d/1PkgPWGSLuVS1eS0dXupZ8EmDh-lXJRW-/view?usp=sharing
Please Help. Thanks a lot!
Solved! Go to Solution.
Hi @adhumal2 ,
How about this?
1. Enter data.
SalesColumn Table:
DiffTable:
2. Create a table.
MonthTable = UNION ( VALUES ( 'Mar 20'[Month ] ), DiffTable )
3. Create measures.
Diff_Sales_Units =
VAR MaxDate =
MAXX ( VALUES ( 'Mar 20'[Month ] ), [Month ] )
VAR MinDate =
MINX ( VALUES ( 'Mar 20'[Month ] ), [Month ] )
RETURN
CALCULATE (
SUM ( 'Mar 20'[Sales Units] ),
FILTER ( 'Mar 20', 'Mar 20'[Month ] = MaxDate )
)
- CALCULATE (
SUM ( 'Mar 20'[Sales Units] ),
FILTER ( 'Mar 20', 'Mar 20'[Month ] = MinDate )
)
Diff_Sales_Value =
VAR MaxDate =
MAXX ( VALUES ( 'Mar 20'[Month ] ), [Month ] )
VAR MinDate =
MINX ( VALUES ( 'Mar 20'[Month ] ), [Month ] )
RETURN
CALCULATE (
SUM ( 'Mar 20'[Sales Value] ),
FILTER ( 'Mar 20', 'Mar 20'[Month ] = MaxDate )
)
- CALCULATE (
SUM ( 'Mar 20'[Sales Value] ),
FILTER ( 'Mar 20', 'Mar 20'[Month ] = MinDate )
)
Measure =
IF (
MAX ( MonthTable[Month ] ) <> "Difference",
SWITCH (
MAX ( SalesColumn[SalesColumn] ),
"Sales Units", CALCULATE (
SUM ( 'Mar 20'[Sales Units] ),
FILTER (
'Mar 20',
'Mar 20'[Month ] = CONVERT ( SELECTEDVALUE ( MonthTable[Month ] ), DATETIME )
)
),
"Sales Value", CALCULATE (
SUM ( 'Mar 20'[Sales Value] ),
FILTER (
'Mar 20',
'Mar 20'[Month ] = CONVERT ( SELECTEDVALUE ( MonthTable[Month ] ), DATETIME )
)
)
),
IF (
MAX ( MonthTable[Month ] ) = "Difference",
SWITCH (
MAX ( SalesColumn[SalesColumn] ),
"Sales Units", [Diff_Sales_Units],
"Sales Value", [Diff_Sales_Value]
)
)
)
4. Create a Matrix visual.
BTW, .pbix file attached.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @adhumal2 ,
How about this?
1. Enter data.
SalesColumn Table:
DiffTable:
2. Create a table.
MonthTable = UNION ( VALUES ( 'Mar 20'[Month ] ), DiffTable )
3. Create measures.
Diff_Sales_Units =
VAR MaxDate =
MAXX ( VALUES ( 'Mar 20'[Month ] ), [Month ] )
VAR MinDate =
MINX ( VALUES ( 'Mar 20'[Month ] ), [Month ] )
RETURN
CALCULATE (
SUM ( 'Mar 20'[Sales Units] ),
FILTER ( 'Mar 20', 'Mar 20'[Month ] = MaxDate )
)
- CALCULATE (
SUM ( 'Mar 20'[Sales Units] ),
FILTER ( 'Mar 20', 'Mar 20'[Month ] = MinDate )
)
Diff_Sales_Value =
VAR MaxDate =
MAXX ( VALUES ( 'Mar 20'[Month ] ), [Month ] )
VAR MinDate =
MINX ( VALUES ( 'Mar 20'[Month ] ), [Month ] )
RETURN
CALCULATE (
SUM ( 'Mar 20'[Sales Value] ),
FILTER ( 'Mar 20', 'Mar 20'[Month ] = MaxDate )
)
- CALCULATE (
SUM ( 'Mar 20'[Sales Value] ),
FILTER ( 'Mar 20', 'Mar 20'[Month ] = MinDate )
)
Measure =
IF (
MAX ( MonthTable[Month ] ) <> "Difference",
SWITCH (
MAX ( SalesColumn[SalesColumn] ),
"Sales Units", CALCULATE (
SUM ( 'Mar 20'[Sales Units] ),
FILTER (
'Mar 20',
'Mar 20'[Month ] = CONVERT ( SELECTEDVALUE ( MonthTable[Month ] ), DATETIME )
)
),
"Sales Value", CALCULATE (
SUM ( 'Mar 20'[Sales Value] ),
FILTER (
'Mar 20',
'Mar 20'[Month ] = CONVERT ( SELECTEDVALUE ( MonthTable[Month ] ), DATETIME )
)
)
),
IF (
MAX ( MonthTable[Month ] ) = "Difference",
SWITCH (
MAX ( SalesColumn[SalesColumn] ),
"Sales Units", [Diff_Sales_Units],
"Sales Value", [Diff_Sales_Value]
)
)
)
4. Create a Matrix visual.
BTW, .pbix file attached.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@adhumal2 , there nothing like diff between two columns you have to create diff between month like
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
diff = [MTD Sales]-[last MTD Sales]
You can use this measure as diff
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...
Appreciate your Kudos.
@amitchandak - Thank you for your reply.
I used above formula MTD Sales but it does return to me a blank column in the visual. Also, can you please also explain me like why have you mentioned the formula for last MTD (Complete) sales
@adhumal2 , Are you date table and month year in visual from that date table?
MTD complete for the case where you choose a date in middle of month and want last month to be complete
@amitchandakYes, I do have a date table ready.
Also, In the columns i have - current month values and previous month values based on the measure created. I do not have months from date table in the visual.
If i create a measure like -
Current Month= CALCULATE(SUM(Append1[Sales Value (CHF)]),FILTER(Append1,Append1[Month]=MAX(Append1[Month])))
Previous Month = CALCULATE(SUM(Append1[Sales Value (CHF)]),FILTER(Append1,Append1[Month]=MIN(Append1[Month])))
It works , but I want to follow your solution to verify these details
@adhumal2 ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
@amitchandak- Sure, Here attched is the sample data and the output expected (in the excel sheets). Many Thanks in advance for your kind efforts.
https://drive.google.com/file/d/1PkgPWGSLuVS1eS0dXupZ8EmDh-lXJRW-/view?usp=sharing
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 |
---|---|
96 | |
93 | |
82 | |
70 | |
64 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |