Forum Discussion
DAX MEasure
Hi,
I have the below SQL statement which I've created and have the desired output in SQL.
select
companyid,applieddate AppliedMonth
,sum(case when month(applieddate)=month(receiveddate) and year(applieddate)=year(receiveddate) then amount else 0 end) ThisMonthCommission
,sum(case when month(applieddate)=month(receiveddate) and year(applieddate)=year(receiveddate) then 0 else amount end) PriorMonthCommission
from
DealPayableActuals
where companyid=280
group by companyid,AppliedDate
Output:
| CompanyID | AppliedMonth | ThisMonthCommission | PriorMonthCommission |
| 280 | 2019-12-01 00:00:00.000 | 4.000000 | 0.000000 |
| 280 | 2020-01-01 00:00:00.000 | 16.000000 | 0.000000 |
| 280 | 2020-02-01 00:00:00.000 | 1344.600000 | 0.000000
|
| 280 | 2020-05-01 00:00:00.000 | 361.780000 | 12.000000 |
When I tried to create a column using the beow DAX it is not giving desired results.
Can you please let me know if I'm using the "if" statement correctly or we can use "SWITCH" command here?
I actually want the two columns "ThisMonthCommission" and "PriorMonthCommission" based on my above conditions(highlighted).
I'm actually new to Power BI and DAX and hence exploring.
Regards,
Himanshu
Anonymous , That is why I have eomonth , end date of month that will have both month and year
Hi Anonymous ,
You need to create two different columns with the followin syntax:
ThisMonthCommission = IF ( MONTH ( 'Table'[ReceivedDate] ) = MONTH ( 'Table'[AppliedDate] ) && YEAR ( 'Table'[AppliedDate] ) = YEAR ( 'Table'[ReceivedDate] ); 'Table'[Amount]; 0 ) PriorMonthCommission = IF ( MONTH ( 'Table'[ReceivedDate] ) = MONTH ( 'Table'[AppliedDate] ) && YEAR ( 'Table'[AppliedDate] ) = YEAR ( 'Table'[ReceivedDate] ); 0; 'Table'[Amount] )Basically the syntax is the same as in SQL however since you are not working on the full table (has you do in SQL) you don't need the GROUP BY part since you are already working ar row context.
My only doubt in the values you share is that the two lines is only show prior month values don't know if it's correct or not because the applied date and received date are not date formats but 00:00:0 so this will not get what you need.
I have made some changes in the data making the two fields as dates and it worked properly.
9 Replies
- Greg_Deckler
Community Champion
Anonymous Can you post sample source data that corresponds with expected output?
Generally posting SQL is a non-optimal way to a solution. MFelix can you decipher? Seems like a SUMMARIZE or GROUPBY
Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.Greg_Deckler This is a sample data.
Id CompanyId CompanyName DealId DealName CustomerId CustomerName SupplierId SupplierName DealType DealTypeName StateId StateName SalespersonId SalespersonName ManagerId ManagerName PayoutLevel DealStakeholderId PayableType UsageMonth UsageYear UsageFrom UsageUpto Usage Amount CashReceivedId CashReportId ReceivedDate AppliedDate IsTiered 33 279 NULL 16 test 1 71 abp 38 AEP Energy 1 Electric 19 Maine 8 har Singh NULL NULL 99 16 2 7 2019 00:00.0 00:00.0 10000 3.4 3 4 00:00.0 00:00.0 0 90 279 NULL 2811 Deal For James@Mike1#997563 627 James@Mike#15777 13 ENGIE 1 Electric 30 New Jersey 58 Dinesh Kumar NULL NULL 99 2947 2 6 2019 00:00.0 00:00.0 7901 23.71 15 33 00:00.0 00:00.0 0
- amitchandak
Super User
Anonymous ,
two new columns
ThisMonthCommission = if(eomonth([applieddate],0) =eomonth([receiveddate],0) ,[amount] ,0)
PriorMonthCommission = if(eomonth([applieddate],0) =eomonth([receiveddate],0) ,0,[amount] )company id filter/slicer you can add at the time of analysis. if his need to done for companyid=280
ThisMonthCommission = if(eomonth([applieddate],0) =eomonth([receiveddate],0) && companyid=280 ,[amount] ,0)
amitchandak Thaks for your reply but if I want the year condition as well with the month condition is that possible?
case when month(applieddate)=month(receiveddate) and year(applieddate)=year(receiveddate) then amount else 0 end
- amitchandak
Anonymous , That is why I have eomonth , end date of month that will have both month and year
