Forum Discussion
Filter based metric sum
Hi Guys!
Please I need your help!
I have two measurements, LAST MONTH and CURRENT MONTH, both measures group sales values by CODE.
When I put the two measures together with the CODE field in a table, I have the result of the image below.
But now I need to create a measure that presents me the sum of the LAST MONTH column only when the respective
row of the CURRENT MONTH column is empty.
Can anyone help me?
Below my measurements:
CURRENT MONTH = CALCULATE(SUM(SALES[AMOUNT]),GROUPBY(ID, ID[CODE]))
LAST MONTH = CALCULATE([CURRENT MOTH],DATEADD(CALENDAR[DATE],-1,MONTH))
Hi,
This measure works. Download the PBI file from here.
Measure = SUMX(FILTER(SUMMARIZE(CALCULATETABLE(VALUES('Table'[ID]),DATESBETWEEN(dimCalendar[Date],EDATE(MIN(dimCalendar[Date]),-1),min(dimCalendar[Date])-1)),'Table'[ID],"A",[CURRENT_MONTH],"B",[LAST MONTH]),[A]=0&&[B]>0),[B])Hope this helps.
9 Replies
Hi,
Not sure how much i can help but i would like to try.
Hi Ashish_Mathur
the problem is that this table is generated only from the insertion of the CODE column and the measures in this "virtual" table. My attempts to carry out a filter in measurements have failed. I would need something like, If the CURRENT MONTH column is equal to Blank() then it sums the corresponding values of the LAST MONTH column. And at the end I need to put the result on a card.
- Hi FreemanZ, unfortunately it's not possible because the values of the LAST MONTH and CURRENT MONTH can be different, then my result won't be correct. Example: see the line 2, CODE 6.
hi flaviobdsti
could you provide a minimum viable sample datasets for all the relevant tables and columns?
