LOD to DAX conversion

[Anonymous]

Hi Guys,

I hope you all are doing well.

I am working on a Tableau to Power Bi migration project and stuck in yet another calculation.

I want to convert one LOD to Dax in power BI. Below the the Tableau LOD calculation:

{ FIXED [Parts Id]:sum({ FIXED [Parts Id],[Part Number],[Country]: AVG(if [Year] = int([ParamYear])-1 then [Vol] end )})}

I tried to use below Calculation in Power BI

Var yr= SELECTEDVALUE(CC[Param_year]) return

SUMX(

SUMMARIZE(CC,CC[parts id],CC[part number], cc[country],

"volpyref", AVERAGEX(cc,if(CC[param_year]=yr-1, CC[vol]))),

[volpyref])

The similar calculation is working perfectly when I am using it for [Year] = int([ParamYear]) . I took the variable and passed the same in the Dax, please refer above for more details .

But when its [Year] = int([ParamYear])-1 , it is giving me blank everytime in power BI but there are values showing for tableau.. 

I tried several methods but i am getting the same everytime. I am trying to calculate this to find previous year vol. 

**And year filter is being mandatorily used.

[Anonymous]

Hi Icey,

Apologies for the late reply as i was busy with some other stuffs.

Coming to your provided solution . Atleast this time I am not getting blank, however the value isn't matching to Tableau version.

I will try to provide you sample data after sometime if its possible.

Thanks 

[amitchandak]

@Anonymous , try a measure like

calculate(Average(Table[Vol]), filter(Table, Table[Year] = selectedvalue(Year[Year]), allexcept(Table,Table[Part ID], [Part Number], [Country])))

Allexcept with fact table and dimension

LOD- FIXED (Level of Details): https://youtu.be/hU-cVOwDCvY

[Anonymous]

Hi Amit,  thanks for your reply,

I tried your calculation and many other ways, but the values are not matching. 

var yrs= SELECTEDVALUE(CC[param_year]) return

var yr = CONVERT(yrs,INTEGER) return

sumx( CC,

CALCULATE(AVERAGEx(cc,CC[vol])

,CONVERT(cc[param_year], INTEGER)= (yr-1),ALLEXCEPT(cc,CC[parts_id],CC[country],CC[part_number])

))

[Icey]

Hi @Anonymous ,

Try this:

Measure = 
VAR yr =
    SELECTEDVALUE ( CC[Param_year] )
RETURN
    SUMX (
        SUMMARIZE (
            CC,
            CC[parts id],
            CC[part number],
            cc[country],
            "volpyref",
                CALCULATE (
                    AVERAGE ( CC[vol] ),
                    FILTER ( ALL ( CC ), CC[param_year] = yr - 1 )
                )
        ),
        [volpyref]
    )

If this doesn't work, share some sample data for test.

Reference: How to Get Your Question Answered Quickly - Microsoft Power BI Community

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Scenario1: I havent selected any year from the year slicer and now PY year is coming for me, also I have hardcoded the previous year as 2021.

******* 

Scenario2:

When I have selected an year from the Base year slicer, PY value will vanish either using hardcode or using a variable also. I have also included your solution, but its coming unique value for all. But i should get the PY year value as show with the hard code value

You may also see this coming when i remove country from the table, as i dont need to show it. 

I will anyway be using country filter for my main dashboard.

Please suggest me what needs to be done here. I don't think this filter(all(table_name) is required here. but when I am removing this . I am getting blank here as well