Display date using DAX

[Rohitr33f]

Hi,

I am trying to display the date only using DAX for below query. Can anyone help on this?

select

top 1 creation_date

FROM [ViewStore].[dbo].[ViewTableHFile_Last2Years]

group by creation_date

order by count(filename) desc

Regards

Rohit Rawat

[SpartaBI]

@Rohitr33f try this measure:

Measure = 
VAR _tbl = 
    ADDCOLUMNS(
        VALUES(ViewTableHFile_Last2Years[creation_date]),
        "@Count", CALCULATE(COUNTROWS(ViewTableHFile_Last2Years))
    )
VAR _max_count = MAXX(_tbl,[@Count])
VAR _filterd_tbl = 
    FILTER(
        _tbl,
        [@Count] = _max_count
    )
VAR _result = 
    CONCATENATEX(_filterd_tbl, ViewTableHFile_Last2Years[creation_date], ", ", ViewTableHFile_Last2Years[creation_date], ASC)
RETURN
    _result

This is a link to a sample file I did for this question (ignore the other tables there):
Display date using DAX.pbix

      

Showcase Report – Contoso By SpartaBI

[Rohitr33f]

Thanks for your help SpartBI. 

This solution helped me learn new things but did not give the exact solution as I need. Here i could not see anywhere filename has been considered.

I am trying to achieve the Date when the higher count of files were created in the past and also sperate date for a current year.

If you could any sggestion again then please advice.

Thanks for the help I really appreciate it.

Sample data and I am expecting 2/8/2019 date for all the past years and 6/8/2020 for current (lets say 2020 is current year) seprately :

creation_date	filename	filename count
2/8/2019	190208_Y7QYGY_v1.Bdbadm.xlsx	44
6/8/2020	200608_37F4FX.Alif.docx	13
6/8/2020	200608_37F4FX.Blif.docx	12
6/8/2020	200608_37F6GX.Alif.pdf	1
6/8/2020	200608_37F6GX.Blif.pdf	1
8/20/2020	200820_3LYSTC_v2_0.Cphilibs.pdf	1
9/28/2020	200928_2EW3SN_v2.Adbadm.pdf	1
9/28/2020	200928_2EW3SN_v2.Bdbadm.pdf	1