Forum Discussion
Comparing data using a filter and displaying the difference
Hi,
I have value type date and computer
| Date | Computer |
2021-09 | Computer 1 |
2021-09 | Computer 12 |
2021-09 | Computer 2 |
| 2021-09 | Computer 3 |
| 2021-10 | Computer 1 |
| 2021-10 | Computer 3 |
| 2021-10 | Computer 4 |
| 2021-10 | Computer 5 |
And now, needs to compare, computers from the date 2021-09 with 2021-10 and show the difference
| Date | Computer |
| 2021-09 | Computer 12 |
| 2021-10 | Computer 3 |
| 2021-10 | Computer 4 |
| 2021-10 | Computer 5 |
If I used
IF(computer && weeknum(date,2)=weeknum(today(),2), 1,0 ), I get the information "Cannot convert "*" values of Text type to True/False" or how to make it show a column with the difference in computers on the date 2021-09 and a second kolumn of the difference in computers on the date 2021-10
Hi Anonymous,
Create two calculated table as:
2021_9 = CALCULATETABLE( 'Table', MONTH('Table'[Date])=9 )2021_10 = CALCULATETABLE( 'Table', MONTH('Table'[Date])=10 )Try measure as:
Measure = IF( MAX('Table'[Computer]) in VALUES('2021_10'[Computer]) && MAX('Table'[Computer]) in VALUES('2021_9'[Computer]), 1, 0 )Here is the output:
The pbix is attached.
If you still have some question, please don't hesitate to let me known.
Best Regards,
Link
Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!
4 Replies
Hi Anonymous,
Create two calculated table as:
2021_9 = CALCULATETABLE( 'Table', MONTH('Table'[Date])=9 )2021_10 = CALCULATETABLE( 'Table', MONTH('Table'[Date])=10 )Try measure as:
Measure = IF( MAX('Table'[Computer]) in VALUES('2021_10'[Computer]) && MAX('Table'[Computer]) in VALUES('2021_9'[Computer]), 1, 0 )Here is the output:
The pbix is attached.
If you still have some question, please don't hesitate to let me known.
Best Regards,
Link
Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!
Anonymous , This week or month ?
With help from date /week or month table you can do that.
for a month with date table
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))with a month year table with month year rank
Month Rank = RANKX(all('Date'),'Date'[Year Month],,ASC,Dense)
Meausre
This Month = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])))
Last Month = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])-1)With week or date tbale
new column
Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format
measures
This Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKAPower BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
https://www.youtube.com/watch?v=pnAesWxYgJ8
