dax query

[Arjun_reddy]

Hi Community team,

need you help on one of the dax query, please find the below snap and understand my rrequirement, write a dax query on that,

I have year, period, sales fields, want to find out the growthrate on max year available period sales, the same periods in previous year sales to growth rate

the max year(2022) having 6 periods and the same periods should check in previous year periods if periods are avaiable in previous year should computing the sales.

Thanks,

Arjun Reddy

[Arjun_reddy]

don't take the table reference, just understanding purpose i have shared the snap, 
for suppose which is i have selectedvalue as year, that year all the periods should sumup that is CY and the same periods sales should sumup in previous year that is PY, this CY,PY variables should use for growth rate

for suppose i have selected 2022 year, In this year only 5 periods, 5 periods sales should compute and the same periods should compute the sales for finding growth rate

Note: don;t consider the missed period, for assumption gave the snap 

category	year	Period	sales
chairs	2021	p01	5000
tables	2021	p03	6000
bulbs	2022	p01	6000
sofa	2022	p04	4500
chairs	2022	p02	8000
chairs	2021	p06	6000
chairs	2021	p07	2800
sofa	2022	p05	4700
bulbs	2022	p04	2000
sofa	2021	p13	5500
tables	2021	p12	6000
tables	2022	p06	3400
bulbs	2021	p11	4000

[Ashish_Mathur]

Hi,

So do you want to see the growth rate in a card visual.  You will also need to share a Calendar Table with a column there of Period.

[Arjun_reddy]

don't have the option for sharing and  for uploading the pbix file and data file
create a calendar manually from 01/01/2021 to till now

[Ashish_Mathur · Accepted solution]

Whenever you have the opportunity to, share the download link of the PBI file with a Calendar Table and a Period column in the Calendar Table.

[Arjun_reddy]

Sunchips MP Dash 08_21.pbix

download the pbix file, use the page1 view and use the growthrate_occ measure, If py variable working fine, issue will resolve

[Ashish_Mathur]

Takes me to a sign-in page.  Ensure that in the file there is a Calendar Table with the Period column.

[Arjun_reddy]

I have return the below dax query for the CY getting accurate value, PY variable have the issue only, It is fetching all the periods sum value, I don't want to get it all, which are the periods are available that periods should get in previous year also

VAR selectedyear=SELECTEDVALUE(Sunchips_MP[YEAR])

VAR MAXPERIOD=MAX(Sunchips_MP[PERIOD])

VAR cy =

 SUMX(

        FILTER(Sunchips_MP, Sunchips_MP[YEAR] = SelectedYear&&Sunchips_MP[PERIOD]<=MAXPERIOD),

     Sunchips_MP[TOTAL OCCASIONS]

    )

VAR py =

   SUMX(

     FILTER(Sunchips_MP,Sunchips_MP[YEAR] = SelectedYear-1&&Sunchips_MP[PERIOD]<=MAXPERIOD),

       Sunchips_MP[TOTAL OCCASIONS]

    )

VAR maxyear_GROWTHRATE=(cy-py)/py

[Ashish_Mathur]

Hi,

There are 5 periods in the max year (not 6).  Also, since P3 is not avaiable in the max year, why should that be considered for the previous year?  Please revise your question and clearly show how you got 19% as the result.  Share data in a format that can be pasted in an MS Excel file.