Forum Discussion
Date range if
Chasing what should be a simple formula - I know i could accomplish in excel but DAX throws me at times.
Was aiming to be a measure.
Simply - I will have a measure per month, or example January 2019, and i want to do a count of a column, and if the count of "Under Contract" > "No Contract", then 1, otherwise 0. But only looking at line items for January 2019 in this measure.
I will replicate per month (for 12 months max) and i plan to use some conditional formating to accomplish a requirement.
Below is where i started - its not working. I assume its to do with the date comparison part.
1. Jan =
IF(DATESBETWEEN(APAll[PTRN_POSTED],01/01/2019,31/01/2019),
IF(CALCULATE(COUNT(APAll[UnderContractV4]),
FILTER(APAll,APAll[UnderContractV4]="Under Contract")) > CALCULATE(COUNT(APAll[UnderContractV4]),
FILTER(APAll,APAll[UnderContractV4]="No Contract")),
1,
0))
It would be good if that date range was dynamic also - based on it being January of the current year. I can sort that out myself so far less important :)
Cheers again community
Hi,
Create a Calendar Table and build a relationship from the PTRN_POSTED column of the APAll Table to the Date column of the Calendar Table. In the Calendar Table. extract Year and Month using these calculated column formulas: Year = Year(Calendar[Date]) and Month = FORMAT(Calendar[Date],"mmmm"). To your visual, drag Year and Month from the Calendar Table. Try this measure
IF(CALCULATE(COUNTROWS(APAll[UnderContractV4]),APAll[UnderContractV4]="Under Contract")>CALCULATE(COUNTROWS(APAll[UnderContractV4]),APAll[UnderContractV4]="No Contract"),
1,0)Hope this helps.
3 Replies
Hi,
Create a Calendar Table and build a relationship from the PTRN_POSTED column of the APAll Table to the Date column of the Calendar Table. In the Calendar Table. extract Year and Month using these calculated column formulas: Year = Year(Calendar[Date]) and Month = FORMAT(Calendar[Date],"mmmm"). To your visual, drag Year and Month from the Calendar Table. Try this measure
IF(CALCULATE(COUNTROWS(APAll[UnderContractV4]),APAll[UnderContractV4]="Under Contract")>CALCULATE(COUNTROWS(APAll[UnderContractV4]),APAll[UnderContractV4]="No Contract"),
1,0)Hope this helps.
Cheers Ashish,
My apologies for not explaining that my output is desired to be horizontal. similar to the below.
Your solution would be perfect if i wasn't looking to display the data horizontally as part of an existing table.
Hi,
Create a matrix visual and drag the Year/Month to the column section.
