Sorry, my bad, forgot the CALCULATE in the ADDCOLUMNS:
Test Measure =
VAR _curdate =
MAX ( 'Date'[Date] )
VAR _prevdate =
DATE ( YEAR ( _curdate ), MONTH ( _curdate ) - 12, DAY ( _curdate ) )
VAR filtered_table =
FILTER ( Sales, Sales[Bill Flag] = 1 ),
FILTER ( ALL ( 'Date' ), 'Date'[Date] > _prevdate && 'Date'[Date] <= _curdate )
VAR _table =
SUMMARIZE ( filtered_table, [LoyaltyId], [Bill Flag] ),
"BuyingVal", CALCULATE ( SUM ( Sales[NetSaleAmount] ) ),
"BuyingFreq", CALCULATE ( DISTINCTCOUNT ( Sales[Original Bill Num] ) )
[BuyingVal] > 4500
&& [BuyingFreq] < 3
Did I answer your question? Mark my post as a solution!
Proud to be a Datanaut!
How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Thanks @ImkeF , FYI I have already added calculate in addcolumn earlier. No luck
I don't see what else I can do without any data to compare against here.
So you might consider creating some sample data with source and desired outcome so we can narrow down the problem.
@ImkeF Here i have attached power bi file with sample data.
For Jan 2015 expected result is 80, but we are getting 3.
Refer the first tab for raw data.
THanks, but I cannot spot the measure that returns the correct figures.
What's its name? - and if it isn't included, please include it as well.
@Baskar - The following will give you 80 for Jan 2015 in your file:
Checking 2 =
var _cur = MAX('Date Master'[Start Date])
var _pre = DATE(YEAR(_cur),MONTH(_cur)-12,DAY(_cur))
var _filter =
Orders[Ship Mode]="Standard Class",
FILTER(ALL('Date Master'),'Date Master'[Start Date] >_pre &&
'Date Master'[Start Date] <= _cur))
var _summed =
return COUNTROWS(filter(_summed,[sales] >1000 && [count] <5))
Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.
We spoke with Power BI Super User, Greg Deckler, about his charity work
Continue your learning in our online communities.