expression calculation that is used in SSRS

[Sandy1235]

HI, 

As of now i am using Datediff(Dateinterval.Day,CDate("1/1/" & paraters!year) in ssrs , but we are migtraring teh reports to power bi, how can i rewrite the above expression in power bi(how can i replace above function )

[v-janeyg-msft]

Hi,   @Sandy1235 

You don't seem to put the full code out, there needs to be two dates in datediff() to calculate the interval, same in powerbi.

You can try like DATEDIFF(date1, date2, day).

Reference:

DATEDIFF function (DAX) - DAX | Microsoft Docs

If you can't modify it yourself, please give a sample and the desired result, I will help you.

Best Regards,
Community Support Team _ Janey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Sandy1235]

count(company."test")/Datediff(Dateinterval.Day,CDate("1/1/" & paraters!year),now()))/365))ReportItems(/textbox1").value

This is the total expression i used in my ssrs expressions. Can you help me to replicate same in power bi 

[v-janeyg-msft]

Hi, @Sandy1235 

I can probably understand your needs. Can you share some sample data in powerbi and your desired result to make it easier for me to compare and modify?

[Sandy1235]

Thank you !

here is a sample data 

I want a pace calcualtion from today to end of the year (and the year is in the filter , when i select previosu year date like 04/14/2021 it shoud show that year pace start from the date to end of the year

[v-janeyg-msft]

Hi, @Sandy1235 

Do you have a date table? 

I don't quite understand what you mean, let me describe it, if it's wrong, please point it out.

You only have one date slicer, when you don't use slicer, calculate the time period from today to the end of the year, when you use slicer, calculate the selected date to the end of the year,right?

What do you want this time period to be used for? Just want this time period?

[Sandy1235]

Hi Thanks for the reply, this is the sample data, nothing i am taking from table, just i want the time period slection in the filter . I just have what is target for the year and the companies count till now for the year . so just checking on the pace, say suppose in ssrs i got the value for pace for last year was (count of companies =1025, target 700, diveristy , 62% and pace calculated by the above expression is 114%)

[v-janeyg-msft · Accepted solution]

Hi, @Anonymous 

Try:

measure =
COUNTX (
    FILTER (
        ALL ( table ),
        [CreatedDate] >= MIN ( Table[CreatedDate] )
            && [CreatedDate] <= MAX ( table[CreatedDate] )
    ),
    [COMP]
)

measure =
SUMX (
    FILTER (
        ALL ( table ),
        [CreatedDate] >= MIN ( Table[CreatedDate] )
            && [CreatedDate] <= MAX ( table[CreatedDate] )
    ),
    [Target]
)

measure =
SUMX (
    FILTER (
        ALL ( table ),
        [CreatedDate] >= MIN ( Table[CreatedDate] )
            && [CreatedDate] <= MAX ( table[CreatedDate] )
    ),
    [DIVERSITY]
)

Or

measure =
COUNTX (
    FILTER (
        ALL ( table ),
        [CreatedDate] >= DATE ( YEAR ( TODAY () ), 1, 1 )
            && [CreatedDate] <= TODAY ()
    ),
    [COMP]
)

Did I answer your question? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.

Best Regards,
Community Support Team _ Janey

[Anonymous]

Hey All,

New to PowerBI.  I had someone using the excel datasets and screwed up some formulas that now has the inputs locked out.  Is there a way to go back and use an older version before the changes?