Comparing data using a filter and displaying the difference

[Anonymous]

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

[v-xulin-mstf · Accepted solution]

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]

You are a genius, this is exactly what I needed.

Thank you, thank you, thank you!

[amitchandak]

@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=6LUBbvcxtKA

Power 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

[Anonymous]

What is this - > 'order'[Qty] is computer or what ? I only have two columns date and computer.