SQL to DAX conversion
How to convert below SQL query to a DAX measure. The 'date' case structure should be dymanic to get last 4 months data.
select mnth,M1_Ord,M2_Ord,M3_Ord,M4_Ord,Is_cons_flg,count(outlet_id) outlet_bucket from
(
(
select mnth,outlet_id, dstr_id,dstr_site_cd,M1_Ord,M2_Ord,M3_Ord,M4_Ord,
(case when M1_ord<= M2_Ord and M2_Ord<=M3_ord and M3_ord<=M4_ord then 'Y'
else 'N' end )Is_cons_flg
from (
select mnth,outlet_id,dstr_id,dstr_site_cd,(case when mnth='201901' then ord else 0 end )M1_Ord,
(case when mnth='201902' then ord else 0 end ) M2_Ord,
(case when mnth='201903' then ord else 0 end ) M3_Ord,
(case when mnth='201904' then ord else 0 end ) M4_Ord
from (
select convert(varchar(6),cal_dt,112) mnth,outlet_id,dstr_id,dstr_site_cd,count(distinct bill_doc_num) ord
from [SDDW].[FCT_DSTR_SEC_SLS_DATA]
where cal_dt between '2019-01-01' and '2019-04-30'
and ertm_type_cd='B2B'
and ctry_id=173
group by convert(varchar(6),cal_dt,112),outlet_id,dstr_id,dstr_site_cd
)freq
) cnst
else 'N' end )Is_cons_flg
from (
select mnth,outlet_id,dstr_id,dstr_site_cd,(case when mnth='201901' then ord else 0 end )M1_Ord,
(case when mnth='201902' then ord else 0 end ) M2_Ord,
(case when mnth='201903' then ord else 0 end ) M3_Ord,
(case when mnth='201904' then ord else 0 end ) M4_Ord
from (
select convert(varchar(6),cal_dt,112) mnth,outlet_id,dstr_id,dstr_site_cd,count(distinct bill_doc_num) ord
from [SDDW].[FCT_DSTR_SEC_SLS_DATA]
where cal_dt between '2019-01-01' and '2019-04-30'
and ertm_type_cd='B2B'
and ctry_id=173
group by convert(varchar(6),cal_dt,112),outlet_id,dstr_id,dstr_site_cd
)freq
) cnst
) vv
group by mnth,M1_Ord,M2_Ord,M3_Ord,M4_Ord,Is_cons_flg
