topic Re: SQL Statment to DAX in Report Server

SQL Statment to DAX

LeaRupnik — Wed, 13 Apr 2022 12:23:59 GMT

Hello,

a have sql statment, how to do this in dax.

select CAST(sum([CPP Real_pro_kol])/1000 as INT) as sum_Real_pro_kol ,[CPP Datum_posnetka] as datum_posnetka ,year([CPP Datum_posnetka]) as leto_posnetka ,month([CPP Datum_posnetka]) as mesec_posnetka ,[CPP Leto] as leto ,[CPP Mesec] as mesec from [cisppr].[f CPP Cista prodajna premija] t1 inner join ( select max([CPP Datum_posnetka]) as leto_Datum_posnetka from [cisppr].[f CPP Cista prodajna premija] group by year([CPP Datum_posnetka]) ,month([CPP Datum_posnetka]) ) as t2 on t1.[CPP Datum_posnetka]= t2.leto_Datum_posnetka where [CPP Datum_posnetka] <= '2022-03-28' --[CPP Datum_posnetka] <= '2020-12-31' --and [CPP Leto] =2021 group by year([CPP Datum_posnetka]) ,month([CPP Datum_posnetka]) ,[CPP Datum_posnetka] ,[CPP Leto] ,[CPP Mesec] order by year([CPP Datum_posnetka]) ,month([CPP Datum_posnetka]) ,[CPP Datum_posnetka] ,[CPP Leto] ,[CPP Mesec]

thx

LEa

Re: SQL Statment to DAX

Greg_Deckler — Wed, 13 Apr 2022 12:26:45 GMT

LeaRupnik These generally go better if you provide sample data and expected output versus the SQL code. Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, 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.

Re: SQL Statment to DAX

LeaRupnik — Thu, 14 Apr 2022 07:33:32 GMT

Hello

I am adding a description of what I need. I have data entered in the table for each day. I would like to return only for maximum data in each year plus month. That is, for 2020 and January, I would only need data from 31.1.2020

thanks

Lea

Re: SQL Statment to DAX

v-luwang-msft — Wed, 20 Apr 2022 09:34:02 GMT

Hi LeaRupnik ，

Could you pls share some sample data ,and what you get final by the sql . Remember to remove confidential data.

Best Regards

Lucien