Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
I'm having an issue where I want to retrieve certain sales information by date, but I don't want to filter the entire visual by that date.
ITEM | ENTRY_TYPE | QUANTITY | DATE |
A | SALE | 100 | 8/1/2021 |
A | SALE | 100 | 7/1/2021 |
A | SALE | 100 | 1/1/2015 |
B | SALE | 200 | 1/1/2015 |
B | SALE | 200 | 1/1/2010 |
In the above scenario, I want to to end up with the following report:
ITEM | RECENT SALES (Last 6 months) |
A | 200 |
B | 0 |
I have gotten it to work half-way using filters (Date Filter>Relative> 6 months), but then item B does not show up on the report because it does not have any rows that meet the criteria.
Thanks for your help!
Solved! Go to Solution.
Hi @jasonwq ,
You can create a measure as below:
RECENT SALES (Last 6 months) =
CALCULATE (
SUMX (
'Table',
IF (
DATEDIFF ( 'Table'[DATE], TODAY (), MONTH ) < 6
&& DATEDIFF ( 'Table'[DATE], TODAY (), MONTH ) >= 0,
'Table'[QUANTITY],
0
)
)
)
Best Regards
Hi @jasonwq ,
You can add one if condition at last like below:-
return if(result = 0,0,result)
It will help you get items which are not having sales in 6 months but they are present.
Thanks,
Samarth
Best Regards,
Samarth
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin
I don't understand. Where do I put that code?
Could you please share your code which you have written, I will add this code into it accordingly
Best Regards,
Samarth
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin
Sorry, I don't have any code written. I've been using the Filters in PowerBI but this is not working well for me.
Hi @jasonwq ,
You can create a measure as below:
RECENT SALES (Last 6 months) =
CALCULATE (
SUMX (
'Table',
IF (
DATEDIFF ( 'Table'[DATE], TODAY (), MONTH ) < 6
&& DATEDIFF ( 'Table'[DATE], TODAY (), MONTH ) >= 0,
'Table'[QUANTITY],
0
)
)
)
Best Regards
User | Count |
---|---|
122 | |
69 | |
67 | |
58 | |
52 |
User | Count |
---|---|
187 | |
94 | |
67 | |
63 | |
54 |