Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello 🙂
I'm doing a matrix table, with one date by column, a date by row and a calculated measure in values:
I'm having a problem with the total by column, the value is not showing. The measure code is:
Stock =
VAR _month = SELECTEDVALUE(MonthTable[MonthCode],201801)
VAR _vYEARMONTH = DATE(LEFT(_month,4),MID(_month,5,2),"1")-1
VAR _YEARMONTH = CONVERT(FORMAT(_vYEARMONTH,"YYYYMM"),INTEGER)
var _value = SWITCH(True(),
FIRSTNONBLANK(QueryA[CONTRACT_MONTH],1) = _YEARMONTH,
var _previousmonths = CALCULATETABLE(VALUES(QueryA[CONTRACT_MONTH]), QueryA[CONTRACT_MONTH]<_YEARMONTH)
return CALCULATE(sum(QueryA[STOCK]), _previousmonths),
FIRSTNONBLANK(QueryA[CONTRACT_MONTH],1) < _YEARMONTH, BLANK(),
FIRSTNONBLANK(QueryA[CONTRACT_MONTH],1) > _YEARMONTH, sum(QueryA[STOCK])
)
return _value
Can someone help with this issue? What i'm doing wrong?
Thank you for your time.
Solved! Go to Solution.
Hi @rcaeiro,
Try measure as:
Measure =
SUMX(VALUES(TableA[CONTRACT_MONTH]),[Stock1])
Here is the output:
Best Regards,
Link
Hi @rcaeiro,
Try measure as:
Measure =
SUMX(VALUES(TableA[CONTRACT_MONTH]),[Stock1])
Here is the output:
Best Regards,
Link
You should have a Date table so you don't have to deal with generating your dates inside your measure like that. Please see this link on how to do that.
Creating a simple date table in DAX - SQLBI
Having said that, the SELECTEDVALUE is returning blank in your Total calculation. You can get around that by using your existing measure in a new measure like this.
NewMeasure = SUMX(VALUES(MonthTable[MonthCode]), [Stock])
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Hello Pat, thank you for your feedback.
I've converted the "MonthTable" into a Date Table, and calculated the month code that i want.
Now i've the measure like that:
Stock =
VAR _month = SELECTEDVALUE(MonthTable[MonthCode1],201801)
var _value = SWITCH(True(),
FIRSTNONBLANK(QueryA[CONTRACT_MONTH],1) = _month,
var _previousmonths = CALCULATETABLE(VALUES(QueryA[CONTRACT_MONTH]), QueryA[CONTRACT_MONTH]<_month)
return CALCULATE(sum(QueryA[STOCK]), _previousmonths),
FIRSTNONBLANK(QueryA[CONTRACT_MONTH],1) < _month, BLANK(),
FIRSTNONBLANK(QueryA[CONTRACT_MONTH],1) > _month, sum(QueryA[STOCK])
)
return _value
And a new measure:
Stock2 = SUMX(VALUES(MonthTable[MonthCode1]), [Stock])
But the column with total still empty 😞
Do you have any suggestion?
Hi @rcaeiro,
I can not reproduce your issue with the screenshot, could you provide sample data?
Best Regards,
Link
Hello @v-xulin-mstf,
Please find a sample is this link: https://wetransfer.com/downloads/4f663da07cab769c3a4b51762a54893020210215124040/57854fa29fe1ca769bb5...
Thank you!
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |