Forum Discussion

Arjun_reddy's avatar
Arjun_reddy
Helper I
3 years ago
Solved

dax query

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 ou...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    3 years ago

    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's avatar
Arjun_reddy
Helper I
3 years ago

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 

 

categoryyearPeriodsales
chairs2021p015000
tables2021p036000
bulbs2022p016000
sofa2022p044500
chairs2022p028000
chairs2021p066000
chairs2021p072800
sofa2022p054700
bulbs2022p042000
sofa2021p135500
tables2021p126000
tables2022p063400
bulbs2021p114000
    
  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    3 years ago

    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's avatar
      Arjun_reddy
      Helper I
      3 years ago

      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

       

      • Arjun_reddy's avatar
        Arjun_reddy
        Helper I
        3 years ago
        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