I cant be able to show list of projects in Table level

[Anonymous]

Hello Team,

Please help me to resolve this issue in DAX()

Issue: I cant be able to show list of projects in Table level. I'm geeting error like below image.

* Projects < 30 Days =

var a=MONTH( SELECTEDVALUE('Compliance Raw Data'[Month & Year])-1)

var c= if(FORMAT(max('Compliance Raw Data'[Today]),"DD")="31","30",FORMAT(max('Compliance Raw Data'[Today]),"DD"))

var d = FORMAT(max('Compliance Raw Data'[Today]),"YYYY")

var e =CONVERT( CONCATENATE(a&"-",CONCATENATE(c&"-",d)),DATETIME)

var e1 =CONVERT(CONCATENATE(MONTH(SELECTEDVALUE('Compliance Raw Data'[Month & Year]))&"-",CONCATENATE(c&"-",d)),DATETIME)

var lastdate1= if(a=MONTH(TODAY())-1,TODAY(),e1)

return

CALCULATE(DISTINCTCOUNT('Compliance Raw Data'[Project ID]),('Compliance Raw Data'[Project Start Date]<=lastdate1 &&CONVERT( 'Compliance Raw Data'[Project Start Date],DATETIME)>e))

error: 

linked in Image

[v-yingjl · Accepted solution]

Hi @Anonymous ,

Please disable the Total in the format pane of the table visual first, then put the measure in it, it should get the correct result.

This because you are using selectedvalue() in your measure, when you enable Total label, it will return a blank value. In this case, it will return "--" so it cannot be converted into datetime type.

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , check if e is returning date or not. try like

CALCULATE(DISTINCTCOUNT('Compliance Raw Data'[Project ID]),('Compliance Raw Data'[Project Start Date]<=lastdate1 && 'Compliance Raw Data'[Project Start Date]>e))

[Anonymous]

Still facing same error

[lkalawski]

@Anonymous 

Can you share sample of data? It will be easier to check your measure and find the issue.

_______________
If I helped, please accept the solution and give kudos! 😀

[lkalawski]

Hi, 

try to use DATE instead of CONCATENATE function in the VAR e:

* Projects < 30 Days =
VAR a =
    MONTH ( SELECTEDVALUE ( 'Compliance Raw Data'[Month & Year] ) - 1 )
VAR c =
    IF (
        FORMAT ( MAX ( 'Compliance Raw Data'[Today] ), "DD" ) = "31",
        "30",
        FORMAT ( MAX ( 'Compliance Raw Data'[Today] ), "DD" )
    )
VAR d =
    FORMAT ( MAX ( 'Compliance Raw Data'[Today] ), "YYYY" )
VAR e =
    CONVERT ( DATE ( d, a, c ), DATETIME )
VAR e1 =
    CONVERT (
        CONCATENATE (
            MONTH ( SELECTEDVALUE ( 'Compliance Raw Data'[Month & Year] ) ) & "-",
            CONCATENATE ( c & "-", d )
        ),
        DATETIME
    )
VAR lastdate1 =
    IF ( a = MONTH ( TODAY () ) - 1, TODAY (), e1 )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Compliance Raw Data'[Project ID] ),
         (
            'Compliance Raw Data'[Project Start Date] <= lastdate1
                && CONVERT ( 'Compliance Raw Data'[Project Start Date], DATETIME ) > e
        )
    )

_______________
If I helped, please accept the solution and give kudos! 😀